setting a variable based on query in dynamic SQL?
-
I really want a simple way of assigning a variable's value to a value returned by dynamic SQL. Simplified example below gives syntax error, however you get the idea of what I am trying to do. Is there a way to do this?
--Begin sample code
DECLARE @num int
DECLARE @SQL varchar(4000)
SELECT @SQL = 'SELECT 33'
SELECT @num = EXEC(@SQL)
Print @num
--End sample code
This will give a syntax error because the following statement does not work....
SELECT @num = EXEC(@SQL)
Nor does...
EXEC @num = (@SQL)
Any ideas other than:
1. Putting the dynamic SQL in a separate sp.
2. Using a table to have the dynamic SQL dump the value into and then setting the variable to that....
A.J.
DBA with an attitude -
Here's a sample that I have saved on my harddrive. It should get you going...
declare
@command nvarchar(1000),
@parmlist nvarchar(100),
@fname varchar(20),
@lname varchar(20)
set @lname = 'accorti'
set @command = N'select @fname = fname from pubs.dbo.employee where job_lvl = 35 and lname = @lname'
set @parmlist = N'@lname AS VARCHAR(20), @fname as VARCHAR(20) OUTPUT'
EXEC sp_executesql @command, @parmlist, @lname, @fname OUTPUT
select @fname
-- Steve
-
Mucho Gracais!
A.J.
DBA with an attitude -
This and many more informations on dynamic sql you can find here:
http://www.sommarskog.se/dynamic_sql.html
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply