Hi, First thing: SUSPEND is only used if the “caller” will ask for multiple rows, returning rows to the client or a caller-stored procedure. In your IN clause, this doesn’t make any sense, as an IN clause isn’t “looping over result”.
Next, an IN clause requires a list of values or a SELECT statement. A FOR EXECUTE STATEMENT is not a select-statement. If you really need a dynamic statement, you have to use the complete UPDATE in a EXECUTE STATEMENT command: EXECUTE STATEMENT ‘update tblCampaign ... etc etc... ‘ || ‘a.campaignid in (select first ‘ || cnt || ‘ c.campaignid etc etc ‘; But using ROWS instead of FIRST makes using a parameter possible, so it seems: IN (SELECT ... FROM ... ROWS :CNT) With regards, Martijn Tonies Upscene Productions http://www.upscene.com Download Database Workbench for Oracle, MS SQL Server, Sybase SQL Anywhere, MySQL, InterBase, NexusDB and Firebird! From: mailto:[email protected] Sent: Friday, December 12, 2014 8:40 PM To: [email protected] Subject: [firebird-support] 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 ( 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.CAMPAIGNNA 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 = :Campaig 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
