Assigning Parameter to Dynamic SQL statement 1

[trpnbillie]

Hi Everyone!

Does anybody know how to assign a variable to a dynamic sql statement result?

The code below works until I reach the last line where I try to assign my variable... I am very unsuccessful!

---START
DECLARE @Table nvarchar(100)
SET @Table = 'TestTable'
DECLARE @SQL nvarchar(1000)
SET @SQL = 'select Status from ' + @Table + ' where ID = 1'

exec (@SQL)

DECLARE @StatusResult = exec (@SQL) --- this line fails
---END

Many Thanks for your insight!!!!

 

[johnv20]

somebody please correct me if I'm wrong but a variable or recordset returned from within dynamic sql cannot be referenced outside of the statement so there's no way to accomplish this. The only way to do this would be to create a udf & reference the value this way

this is from books online
sp_executesql has the same behavior as EXECUTE with regard to batches, the scope of names, and database context. The Transact-SQL statement or batch in the sp_executesql stmt parameter is not compiled until the sp_executesql statement is executed. The contents of stmt are then compiled and executed as an execution plan separate from the execution plan of the batch that called sp_executesql. The sp_executesql batch cannot reference variables declared in the batch calling sp_executesql. Local cursors or variables in the sp_executesql batch are not visible to the batch calling sp_executesql. Changes in database context last only to the end of the sp_executesql statement.

 

[trpnbillie]

I think I got this to work. Check it out:

DECLARE @Table nvarchar(100)
SET @Table = 'TestTable'
DECLARE @sql nvarchar(4000),
@col sysname,
@result varchar(20)
SELECT @col = N'au_fname'
SELECT @sql = N'SELECT @result = status FROM ' + @Table + N' WHERE ID = 1'
EXEC sp_executesql @sql, N'@result varchar(20) OUTPUT', @result OUTPUT
SELECT @result