How to call compound SQLs within a stored procedure

[cuddles]

Hi all,

Is there any way to call a labeled compound SQL statement from within a stored proc? say i have a procedure which retrieves names and everytime a certain condition occurs, i want the SQL to execute. Is there any way to call the SQL? if there is, whats the syntax?

thanks a lot

[chandu031]

Hi,

There is no way of calling an SQL statement in a stored procedure , but there is a better way. All you have to do is use dynamic SQL.Firstly,you have to store the query string in a variable, then wherever you want to execute the query just use an EXECUTE IMMEDIATE statement. For example:

CREATE PROCEDURE TEST
LANGUAGE SQL
BEGIN

DECLARE V_STATEMENT VARCHAR(32000);

SET V_STATEMENT = 'INSERT INTO TABLE_A VALUES (1)';

IF(CONDTION) THEN
EXECUTE IMMEDIATE V_STATEMENT;
END IF;
............... .....
............... .....
............... .......
IF (CONDITION) THEN
EXECUTE IMMEDIATE V_STATEMENT;
END IF;
............... ......
............... .
END


You just have to take care that the query string stored is proper. If you are not familiar with Dynamic Sql , it's better to read up on it before using it.

Hope this is helpful..

[cuddles]

err, would a goto to a labeled statement containing the SQL work?

Hi,

There is no way of calling an SQL statement in a stored procedure , but there is a better way. All you have to do is use dynamic SQL.Firstly,you have to store the query string in a variable, then wherever you want to execute the query just use an EXECUTE IMMEDIATE statement. For example:

CREATE PROCEDURE TEST
LANGUAGE SQL
BEGIN

DECLARE V_STATEMENT VARCHAR(32000);

SET V_STATEMENT = 'INSERT INTO TABLE_A VALUES (1)';

IF(CONDTION) THEN
EXECUTE IMMEDIATE V_STATEMENT;
END IF;
............... .....
............... .....
............... .......
IF (CONDITION) THEN
EXECUTE IMMEDIATE V_STATEMENT;
END IF;
............... ......
............... .
END


You just have to take care that the query string stored is proper. If you are not familiar with Dynamic Sql , it's better to read up on it before using it.

Hope this is helpful..

[frozenmist]

Ill give u an example for label. Check if ur requirement meets this


create procedure sample
language sql
begin
declare v_flag integer;

label1:
insert into test values('1');
goto label2;

set v_flag=1;
if(v_flag=1) then goto label1;
end if;
label2:
insert into test values('2');
end

Now test will contain
1
2

Cheers

[frozenmist]

May be this would make more sense... According to the value of the rrandom number i am going to a label

create procedure sample
language sql
begin
declare v_flag integer;
set v_flag=rand();
if(v_flag<50) then goto label1;
elseif(v_flag<1 00)then goto label2;
else goto label3;
end if;
label1:
insert into test values('1');
goto label3;
label2:
insert into test values('2');
goto label3;
label3:
insert into test values('conditi ons over');
end

[cuddles]

alrighty then, thanks for the info!