SQL Storing EXEC value in Variable

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

SQL Storing EXEC value in Variable

Author

Topic

techglider
Starting Member

26 Posts

Posted - 2009-07-28 : 17:25:00

I'm trying to basically store a value that an EXEC statement returns in a variable..

i.e

SET @table2 = EXEC ('(' + @date3 + @qry3 + ')')

The reason i have set paren's in the exec is b/c the sql string is dynamic..

So just doing the following will give me the correct results:
EXEC ('(' + @date3 + @qry3 + ')')

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-07-28 : 17:37:29

INSERT TempTable
EXEC ('(' + @date3 + @qry3 + ')')

SELECT @Table2 = Col1 FROM TempTable

N 56°04'39.26"
E 12°55'05.63"

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-07-28 : 17:52:00

you can also use sp_executesql with output variable(s) instead of exec()


declare @outVar varchar(10)
       ,@execSql nvarchar(50)
       ,@outputVal nvarchar(10)

select @execSql = N'select @retVal = convert(varchar, getdate(), 101)'

exec sp_executesql 
       @stmt = @execSql
       ,@params = N'@retVal varchar(10) output'
       ,@retVal = @outputVal output

select @outputVal [output Value]

OUTPUT:
output Value
------------
07/28/2009

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources