MySQL Forums
Forum List  »  Sybase

set multiple element from select in procedure stored
Posted by: Xavier Manach
Date: December 21, 2009 09:12AM

Hi.

I search a better way for my translation into a procedure stored.

First step : how to set 2 variables from a 'select'.

Sybase :
declare a smallint;
declare b smallint;
select a=col_a, b=col_b from my_table where col_c=1;

mysql solution 1 ... not good because I need to request 2 time :
declare a smallint;
declare b smallint;
set a =select (col_a from my_table where col_c=1);
set b =select (col_b from my_table where col_c=1);

mysql solution 2 ... use a cursor... it's the correct way ?:
declare a smallint;
declare b smallint;
DECLARE cur1 CURSOR FOR col_a, col_b from my_table where col_c=1;
OPEN cur1;
FETCH cur1 INTO a, b;
CLOSE cur1;

There is a best way or shortest way ?

Second Step : how test if exist a line.

Sybase :
declare a smallint;
declare b smallint;
select a=coalesce(col_a,0) , b=coalesce(col_b,0) from my_table where col_c=1;

mysql solution 1 ... not good because I need to request 2 time :
declare a smallint;
declare b smallint;
set a =select (coalesce(col_a,0) from my_table where col_c=1);
set b =select (coalesce(col_b,0) from my_table where col_c=1);

mysql solution 2 ... ??? :
declare a smallint;
declare b smallint;
declare done smallint default 0;
DECLARE cur1 CURSOR FOR col_a, col_b from my_table where col_c=1;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
OPEN cur1;
if (done=0) then
FETCH cur1 INTO a, b;
else;
set a=0;
set b=0;
end if;
CLOSE cur1;

Thx.
Xavinsky.

Options: ReplyQuote


Subject
Views
Written By
Posted
set multiple element from select in procedure stored
17040
December 21, 2009 09:12AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.