Razz's Ramblings

Although not recommended for security reasons, Dynamic SQL can prove to be quite handy when it comes to customizing queries to run on multilple tables/databases. There are certain situations when using Dynamic SQL is the better option, at the same time being reasonably safe. I ran into one such situation when I used it during a backend process of transferring records from one database to another, with the user completely oblivious to the whole thing.

Anyway, I ran into a little obstacle when I needed to assign the result of a query being run using dynamic sql to a local variable of a stored procedure. Here's how I resolved it:

-- Declare a variable to hold our sql statement
Declare @sql nvarchar(max)

-- Declare the variable that should store the result
Declare @vcTemp varchar(max)

-- Do a select on a table, basically write any query you want as long as
-- it uses our variable
Select @sql = N'Select top 1 @vcTemp = from '

-- Call the built-in sp_executesql SP specifying our varibale as the
-- output
EXEC sp_executesql @sql, N'@vcTemp varchar(max) output', @vcTemp output

-- Doing a simple select will reveal, the value has been read into it
Select @vcTemp

It was a cool new thing I learned, so I thought I'd share. Hope this helped.