Dynamic SQL in store proc !

Hi ,

I am writing the following procedure in firebird database:

/********************************************************

SET TERM !! ;
CREATE PROCEDURE getTestingOverview(reportView VARCHAR(10),config VARCHAR(50), groupName VARCHAR(50), phaseName VARCHAR(50), dateHigh VARCHAR(15), dateLow VARCHAR(15), ignorePass VARCHAR(5), sortKey VARCHAR(20), sortOrder VARCHAR(20))

RETURNS(
product VARCHAR(128),
uut_key VARCHAR(128),
system_id NUMERIC(18,0),
date_tested TIMESTAMP,
test_script_id NUMERIC(18,0),
result VARCHAR(128),
is_hidden INTEGER,
system_group_name VARCHAR(128),
system_configuration_name VARCHAR(128),
system_phase_name VARCHAR(128))
AS
DECLARE VARIABLE query VARCHAR(1000);
BEGIN

query = 'SELECT
s.product,
s.uut_key,
s.system_id,
ts.date_tested,
ts.test_script_id,
ts.result, s.is_hidden,
sg.system_group_name,
sc.system_configuration_name,
sp.system_phase_name
FROM
system s,
test_script ts,
system_group sg,
system_configuration sc,
system_phase sp
WHERE
s.system_id = ts.system_id AND
sg.system_group_id = ts.system_group_id AND
sc.system_configuration_id = ts.system_configuration_id AND
sp.system_phase_id = ts.system_phase_id AND
ts.date_tested < '' || dateHigh || '' AND
ts.date_tested > '' || dateLow || '' AND
ts.test_script_id
IN
(SELECT
max(test_script_id)
FROM
system s,
test_script ts
WHERE
s.system_id = ts.system_id GROUP BY s.uut_key)
';

IF (config <> 'all') THEN
query = query || ' AND ts.system_configuration_id = '' || config || ''' ;
IF (ignorePass = 'yes') THEN
query = query || ' AND ts.result NOT LIKE '' %PASS%''';
IF (groupName <> 'all') THEN
query = query || ' AND ts.system_group_id = '' || groupName || ''';
IF (phaseName <> 'all') THEN
query = query || ' AND ts.system_phase_id = '' || phaseName || ''';
IF (reportView = 'summary') THEN
query = query || ' ORDER BY '' || sortKey || sortOrder || ''';

FOR EXECUTE STATEMENT :query
INTO
roduct,
:uut_key,
:system_id,
:date_tested,
:test_script_id,
:result,
:is_hidden,
:system_group_name,
:system_configuration_name,
:system_phase_name
DO
BEGIN
SUSPEND;
END
END !!
SET TERM ;!!


**********************************************************/

My procedure is getting compiled properly but I am getting error in forming select statement as you can see, i am placing dateHigh and dateLow in that string but i am constantly getting string coversion error. I tried to use cast function but still in vain.

Can somebody please direct me how i can create sql dynamically and use it in this procedure.

Thanks in advance for helping me to solve this issue.

Regards,

What's the format for the string-DATE that you're using?


With regards,

Martijn Tonies
Database Workbench - tool for InterBase, Firebird, MySQL, Oracle & MS SQL Server
Upscene Productions
http://www.upscene.com

Hi,

Thanks for replying and taking your time to answer this question.

I have finally changed my procedure little bit and it worked for me. Here is the complete procedure showing how to create dynamic sql and use it in the store procedure:

------------------------------------------------------
SET TERM !!;
CREATE PROCEDURE get_Testing_Overview(
reportView VARCHAR(10),
config VARCHAR(50),
groupName VARCHAR(50),
phaseName VARCHAR(50),
dateHigh TIMESTAMP,
dateLow TIMESTAMP,
ignorePass VARCHAR(5),
sortKey VARCHAR(20),
sortOrder VARCHAR(20)
)
RETURNS(
product VARCHAR(128),
uut_key VARCHAR(128),
system_id NUMERIC(18,0),
date_tested TIMESTAMP,
test_script_id NUMERIC(18,0),
result VARCHAR(128),
is_hidden CHAR(1),
system_group_name VARCHAR(128),
system_configuration_name VARCHAR(128),
system_phase_name VARCHAR(128))
AS
DECLARE VARIABLE query VARCHAR(1000);

BEGIN
query = 'SELECT
s.product,
s.uut_key,
s.system_id,
ts.date_tested,
ts.test_script_id,
ts.result, s.is_hidden,
sg.system_group_name,
sc.system_configuration_name,
sp.system_phase_name
FROM
system s,
test_script ts,
system_group sg,
system_configuration sc,
system_phase sp
WHERE
s.system_id = ts.system_id AND
sg.system_group_id = ts.system_group_id AND sc.system_configuration_id = ts.system_configuration_id AND
sp.system_phase_id = ts.system_phase_id AND
ts.date_tested < ''' || dateHigh || ''' AND
ts.date_tested > ''' || dateLow || ''' AND
ts.test_script_id
IN
(SELECT
max(test_script_id)
FROM
system s,
test_script ts
WHERE
s.system_id = ts.system_id GROUP BY s.uut_key)
';

IF (config <> 'all') THEN
query = query || ' AND ts.system_configuration_id = ' || config ;

IF (ignorePass = 'yes') THEN
query = query || ' AND ts.result NOT LIKE %PASS%';

IF (groupName <> 'all') THEN
query = query || ' AND ts.system_group_id = ' || groupName;

IF (phaseName <> 'all') THEN
query = query || ' AND ts.system_phase_id = ' || phaseName;
IF (reportView = 'summary') THEN
query = query || ' ORDER BY ' || sortKey || ' ' || sortOrder;
FOR EXECUTE STATEMENT :query
INTO
roduct,
:uut_key,
:system_id,
:date_tested,
:test_script_id,
:result,
:is_hidden,
:system_group_name,
:system_configuration_name,
:system_phase_name
DO
BEGIN
SUSPEND;
END
END !!
SET TERM :!!
------------------------------------------------------

Thanks once again.