How to use the RETURN keyword in a MySQL Stored Procedure?

[vijjibalu]

Hi all,
as part of my project requirement we are migrating procedures from mssql to mysql
below is the code in mssql

DECLARE 
     "@rowcnt" int
    select"@rowcnt" = count(*) from tg_services WHERE name ="@name"
    IF (@rowcnt <> 0)
    BEGIN
      -- print"@name" + ' already exists in the tg_services table'
      return
    end
    else
    BEGIN
      select"@rowcnt" = count(*) from tg_services WHERE service_id ="@serviceid"
      IF (@rowcnt <> 0)
      BEGIN
        --print 'serviceid '+ convert(varchar(10),@serviceid) + ' already exists in the tg_services table'
        return
      end
      else
      BEGIN
        INSERT into tg_services VALUES(@serviceid,@name)
      end
    end
  end
 

my question is:
in the above code they are returning from the procedure if rowcount <>0
else
doing some other bussiness
how do i get the same behaviour in mysql?
please do the need full.

Thanks in Advance
Vijaya

[Atli]

Hi.

The RETURN keyword is not used in MySQL procedures.

If you need a stored procedure to return a value, you should use a FUNCTION.

But, if you need to exit a stored procedure, as done in your MSSQL example, you can use the LEAVE keyword.
That this would require you to "label" your BEGIN ... END block.

For example:

CREATE PROCEDURE SelectTest()
root:BEGIN
    SELECT 'First';
    LEAVE root;
    SELECT 'Second';
END;
 

There, the second SELECT will never be executed.

Also, just to put it out there.
Your problem could also be solved by simply altering the flow of your code.

Rather than checking if the value IS something, and calling the RETURN keyword, only performing some action if the ELSE clause is reached...
Try checking if the value IS NOT something, and simply perform the action then, letting the function end if the value IS something.

For example:

/* Rather than doing */
root:BEGIN
    DECLARE s INT DEFAULT 0;
    SELECT COUNT(*) INTO s FROM someTable;
 
    IF s =  0 THEN
        LEAVE root;
    ELSE
        /* Perform some action */
    END IF;
END;
 
/* Do */
BEGIN
    DECLARE s INT DEFAULT 0;
    SELECT COUNT(*) INTO s FROM someTable;
 
    IF s != 0 THEN
        /* Perform some action */
    END IF;
END;