Return true from stored procedure

raydona

Hi, I have never created a stored procedure so please bear with me. I wish to create a stored procedure where if a condition is met true is returned, else false is returned, something like:
CREATE PROCEDURE SomeName(IN X VARCHAR(15), IN Y VARCHAR(30))
BEGIN
SELECT A, B
FROM Table
//Go through table and
IF A == X AND B == Y return true, else return false
END;

How can I achieve this? I will be grateful for all help.

Lars_Berg

A procedure does not return a value, but it is possible to use an out parameter

create procedure p(x varchar(15),y varchar(30),out p boolean)
reads sql data 
    set p = exists (select * from t where a = x and b = y)

It might be more apropriate to use a function though as these may return a value

create function f(x varchar(15),y varchar(30)) returns boolean
reads sql data 
    return exists (select * from t where a = x and b = y)

The above code is standard SQL but most DBMS does not comply with the standard with regard to stored procedures and functions. This means that you may have to change the above code depending on which DBMS you are using.

raydona

Lars,
Many thanks for your help. I am not too familiar with SQL syntax, my background is C++ programming. I would be grateful if you could let me know if the following SQL function is correct. If not please let me know the correct way to construct the function.

CREATE FUNCTION VerifyEmployee (IN surname VARCHAR(15), IN email VARCHAR(30))
RETURNS INT
BEGIN
SELECT LastName,EmailAddress FROM Employee
IF(LastName = surname AND EmailAddress = email)
THEN return 1;
ELSE return 0;
END IF;
END;

Further, how can I use the above function in a PHP statement, e.g. can I say something like:
<?php
$con = .............
$answer = 0;
//‘surname’ and ‘email’ are entered by user
$surname=mysql_real_escape_string($POST['surname']);
$email=mysql_real_escape_string($POST['email']);

$sql= mysql_query("$answer = call VerifyEmployee($secondname, $email)");

if($answer == 1) echo "Employee verified”;
else echo "Employee not verified”;

if (!mysql_query($sql,$con))
{ die('Error: ' . mysql_error());
}
mysql_close($con);
?>