Hi,

I am trying to write a dynamic sql in procedure (i m writing a fixed list dyanmic sql).

/**

CREATE PROCEDURE get_systems(
in groupInfo VARCHAR(50),
in groupId BIGINT,
in configInfo VARCHAR(50),
in configId BIGINT,
in phaseInfo VARCHAR(50),
in phaseId BIGINT,
in aliasInfo VARCHAR(50),
in aliasId BIGINT,
in dateHigh TIMESTAMP,
in dateLow TIMESTAMP,
in statusSel VARCHAR(50)
)
BEGIN
declare system_id BIGINT ;
declare uut_key VARCHAR(128);
declare alias VARCHAR(128);
declare product VARCHAR(128);
declare is_running CHAR(1);
declare result VARCHAR(128);
declare manufacturer_name VARCHAR(128);
declare query VARCHAR(1000);

set query = 'SELECT
s.system_id,
s.uut_key,
a.alias,
s.product,
tr.result,
ts.is_running,
m.manufacturer_name
FROM
system s,
system_hidden_state sh,
system_alias a,
manufacturer m,
test_script ts,
test_result tr
WHERE
sh.system_id = s.system_id AND
sh.is_hidden = ''N'' AND
s.alias_id = a.id AND
s.manufacturer_id = m.manufacturer_id AND
s.last_result_id = tr.id AND
ts.start_date < ''' || ? || ''' AND
ts.start_date > ''' || ? || ''' AND
ts.system_id = s.system_id ';

IF (groupInfo = 'group' ) THEN
set query = query || ' AND s.system_group_id = ' || ? ;
End if;

IF (configInfo = 'config') THEN
set query = query || ' AND s.system_configuration_id = ' || ? ;
End if;

IF (phaseInfo = 'phase') THEN
set query = query || ' AND s.system_phase_id = ' || ? ;
End if;

IF (aliasInfo = 'aView') THEN
set query = query || ' AND a.id = ' || ? ;
End if;

IF (statusSel <> 'status') THEN
set query = query || ' AND tr.result =''' || ? || '''';
End if;

set query = query || ' order by s.uut_key, ts.start_date desc' ;

Exec sql declare c1 cursor for DYNSQL;
Exec sql prepare DYNSQL from query ;
Exec open c1 using :dateHigh, :dateLow, :groupId, :confiId, haseId, :aliasId, :statusSel;

Exec sql
fetch c1 into system_id, uut_key, alias, product, result, is_running, manufacturer_name;
Exec sql close c1;
END @

*/

When i tried to compile the above procedure i got the following error.

/*

SQL0104N An unexpected token "sql" was found following "desc' ; Exec". Expected tokens may include: ":". LINE NUMBER=48. SQLSTATE=42601
*/

I am not able to figure out why it is throwing error on Exec statement. Am i missing something or doing something drastically wrong?

I would appreciate if you please have a look into above procedure and error it is throwing.

Thanks!