We help IT Professionals succeed at work.

SET @variable = (EXECUTE(@mySQL))

teberhardt
teberhardt asked
on
Medium Priority
4,190 Views
Last Modified: 2006-11-17
Hi guys,

Here is the problem:

DECLARE @variable smallint
DECLARE @mySQL varchar(100)

SET @mySQL = 'Dynamic SQL statement that produces a result set of one smallint value'

--THIS RETURNS A RESULT SET
EXECUTE(@mySQL)

--THIS WILL NOT PUT THE RESULT SET INTO THE VARIABLE
SET @variable = (EXECUTE(@mySQL))
SELECT @variable

I need the value produced by (EXECUTE(@mySQL)) to be stored in @variable that will be used in another dynamic SQL statement.  How do I do this?

Thanks,
Todd
Comment
Watch Question

Senior DBA
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019
Commented:
The only thing I've found that works (in SQL 7.0 anyway) is to put the value into a global temp table within the EXEC and then access that from the original calling proc.  It's a pain but I don't know of another way to do it.  For example:

CREATE TABLE ##hold (result SMALLINT)
DECLARE @result SMALLINT

EXEC 'INSERT INTO ##hold ' + @mysql
SET @result = (SELECT result FROM ##hold)
Scott PletcherSenior DBA
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019

Commented:
Oops, typing too quickly, omitted parentheses from EXEC, should be "EXEC ('INSERT INTO ##hold' + @mysql)"

Author

Commented:
It worked great with
EXEC 'INSERT INTO ##hold ' + @mysql
 changed to
EXEC ('INSERT INTO ##hold ' + @mysql)

Thanks a bunch!

Commented:
Not sure if this applies.  Anyway....

Does the @mySQL update, insert or delete records?  

I have seen it where SQL return the "(X row(s) affected)" rather that the "recordset" of a sql statement.  

When I've added "set nocount on" to the SQL statement it seemed to fix the problem.

Author

Commented:
The @mySQL is strictly a SELECT statement that produces a result set of one value.  Thanks for the extra info, though.

Ciao!