procedure return True or False

Question

I need a stored procedure in firebird 3.0 to return True, False or 1,0 result to check if this person or the record exists in database. If exists the procedure should return true, if not return False, or return 1, if not return 0

This is my procedure but it gives me an error:

Invalid token.
Dynamic SQL Error.
SQL error code = -104
Token unknown - line 10, column 1.
end.

I want to check if the record is exist or not by checking the first name last name and day of birth.

create procedure aa(
v varchar(20),
g varchar(20),
dd date)
as 
begin
select fname,lname,bday from STUDENT
where not exists (select fname,lname,bday from STUDENT  where fname=:v and lname=:g and bday=:dd)
end

Also, explain exactly what you are trying to achieve (eg under what conditions should it return true?), because besides its obvious syntax errors, this procedure doesn't attempt in any way to return a boolean value, so it is not really obvious how to fix this procedure beyond fixing the syntax errors. — Mark Rotteveel, Aug 16 '20 at 07:21
I want to check if this record is exist or not check it with the first naame LastName Day of birth — Sahat, Aug 16 '20 at 13:06
And the error you get? And please add details in an [edit] to your question. Don't add them in the comments. — Mark Rotteveel, Aug 16 '20 at 13:12
i edited my question post the database do not save my procedure what i i want is check if this person or the record exist in database — Sahat, Aug 16 '20 at 20:44
Do you have programming experience in classic imperative languages (C, Java, Pascal, Basic) ? Problem is, your code does not have a specific error, it just has no sense... Like few random commands copy-pasted from random places without understanding how and why they even do work. You may read the `Procedural SQL` chapter of the docs - but it assumes you have a basic understanding of imperative languages programming and it adapts your generic skills to PSQL specifics, but if you don't have those it will not help you. — Arioch 'The, Aug 17 '20 at 00:27
The docs: http://firebirdsql.org/file/documentation/html/en/refdocs/fblangref25/firebird-25-language-reference.html#fblangref25-psql ~~~ your procedure does not have any output parameters, so it can not "return" no any value ~~~ outer `select` in your proc has nowhere to save the results to ~~~ would your outer `select` return less than one row or more than one row - there is no handling for it. /// Those gaps are obvious but taken together... Even making some code, that does not crash with error s, would not mean it returns correct, expected data in all cases — Arioch 'The, Aug 17 '20 at 00:30
i add a return variable but the same problem create procedure aa( v varchar(20), g varchar(20), dd date) returns (aa integer)as begin select fname,lname,bday from STUDENT where not exists (select fname,lname,bday from STUDENT where fname=:v and lname=:g and bday=:dd) end — Sahat, Aug 17 '20 at 15:15
the samecreate procedure aa( v varchar(20), g varchar(20), dd date) returns (result boolean) as begin result=(select fname,lfname,bday from STUDENT where not exists (select fname,lname,bday from STUDENT where fname=:v and lfname=:g and bday=:dd)); end; — Sahat, Aug 17 '20 at 15:27

Mark Rotteveel · Accepted Answer · 2022-07-19T05:55:29.860

Your stored procedure doesn't work because 1) it doesn't have a RETURNS clause so it cannot return true or false, and 2) a select in PSQL requires an INTO clause to put the values into a variable or return-parameter.

Based on your code, a stored procedure that does what you want would be:

create procedure aa(
  v varchar(20),
  g varchar(20),
  dd date)
  returns (student_exists boolean)
as 
begin
  student_exists = exists (select * from STUDENT where fname=:v and lname=:g and bday=:dd);
end

Depending on what you're need to achieve, a function might be more appropriate:

create function aa(
  v varchar(20),
  g varchar(20),
  dd date)
  returns boolean
as 
begin
  return exists (select * from STUDENT where fname=:v and lname=:g and bday=:dd);
end

procedure return True or False

1 Answers1