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
(
CampaignName Varchar(75)
, BatchNum INT
, Cnt INT
)
RETURNS
(
CntBatch INT
)
AS
DECLARE VARIABLE VSQL VARCHAR(1024);
BEGIN
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
INTO :CntBatch;
SUSPEND;
END^
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
FOR
