[vijjibalu]

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

Expand|Select|Wrap|Line Numbers

1. DECLARE 
2.      "@rowcnt" int
3.     select"@rowcnt" = count(*) from tg_services WHERE name ="@name"
4.     IF (@rowcnt <> 0)
5.     BEGIN
6.       -- print"@name" + ' already exists in the tg_services table'
7.       return
8.     end
9.     else
10.     BEGIN
11.       select"@rowcnt" = count(*) from tg_services WHERE service_id ="@serviceid"
12.       IF (@rowcnt <> 0)
13.       BEGIN
14.         --print 'serviceid '+ convert(varchar(10),@serviceid) + ' already exists in the tg_services table'
15.         return
16.       end
17.       else
18.       BEGIN
19.         INSERT into tg_services VALUES(@serviceid,@name)
20.       end
21.     end
22.   end
23.  

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:

Expand|Select|Wrap|Line Numbers

1. CREATE PROCEDURE SelectTest()
2. root:BEGIN
3.     SELECT 'First';
4.     LEAVE root;
5.     SELECT 'Second';
6. END;
7.  

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:

Expand|Select|Wrap|Line Numbers

1. /* Rather than doing */
2. root:BEGIN
3.     DECLARE s INT DEFAULT 0;
4.     SELECT COUNT(*) INTO s FROM someTable;
5.  
6.     IF s =  0 THEN
7.         LEAVE root;
8.     ELSE
9.         /* Perform some action */
10.     END IF;
11. END;
12.  
13. /* Do */
14. BEGIN
15.     DECLARE s INT DEFAULT 0;
16.     SELECT COUNT(*) INTO s FROM someTable;
17.  
18.     IF s != 0 THEN
19.         /* Perform some action */
20.     END IF;
21. END;
22.