how to set variable using dynamic sql

[Bill Smith]

If I have the line following in a stored proc...
set rowCount = (select count(*) from t2);

what is the equivalent way to set rowCount variable using dynamic sql...
...
set sqlStmt = 'select count(*) from t2';
EXECUTE IMMEDIATE sqlStmt;
...

Thanks, Bill

[Rob Wilson]

Bill Smith wrote:

If I have the line following in a stored proc...
set rowCount = (select count(*) from t2);

what is the equivalent way to set rowCount variable using dynamic sql...
..
set sqlStmt = 'select count(*) from t2';
EXECUTE IMMEDIATE sqlStmt;
..

Thanks, Bill

The next statement can be:

GET DIAGNOSTICS rowCount = ROW_COUNT;

or you can instead do:

DECLARE v_stmt STATEMENT;
DECLARE c1 CURSOR FOR v_stmt;
....
PREPARE v_stmt FROM sqlStmt;
OPEN c1;
FETCH c1 INTO rowCount;
--
Rob Wilson
ro********@ameritech.net.invalid (remove .invalid)