|Subject||How do you write dynamic sql in Firebird|
I'm trying to write a procedure where the number you select is a parameter. This is what I've come up with, but it doesn't work.
SET TERM ^ ;
CREATE PROCEDURE uspInitCampaignMaxCnt
, BatchNum INT
, Cnt INT
DECLARE VARIABLE VSQL VARCHAR(1024);
SQL = 'SELECT FIRST ' || Cnt || ' c.CAMPAIGNID
FROM tblCampaign c
WHERE c.CAMPAIGNNAME = :CampaignName
AND c.CONTACTPERMISSION = ''Y''
AND c.BADEMAIL = ''N''
AND c.ABORTCAMPAIGN = ''N''
AND c.BATCHNUM = -1';
UPDATE tblCampaign a SET
a.BATCHNUM = :BatchNum
WHERE a.CAMPAIGNID IN (FOR EXECUTE STATEMENT VSQL DO SUSPEND; );
SELECT COUNT(a.CampaignId) FROM tblCampaign a
WHERE a.CAMPAIGNNAME = :CampaignName
AND a.BATCHNUM = :BatchNum
SET TERM ; ^
It doesn't like the 'FOR' ... for that matter it hasn't liked anything I've tried there. Here's the error:
Engine Code : 335544569
Engine Message :
Dynamic SQL Error
SQL error code = -104
Token unknown - line 27, column 28