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!

 

 

 

 

 

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