set local variable when constructing dynamic query

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

SQL Server 2000 Forums

Transact-SQL (2000)

set local variable when constructing dynamic query

Author

Topic

the_justin
Starting Member

19 Posts

Posted - 2009-03-30 : 04:17:19

Hi all,
I'm trying to set up a local variable when constructing dynamic query but can't do that.
to make it clear, here is the example:

-----------------------------------------
declare @cnt int
declare @sql nvarchar(300)
-- assuming i have a table called blah

set @sql = 'select @cnt = 1 from blah'
execute (@sql)

-----------------------------------------
i kept getting an error message saying that i have to declare @sql.
anybody has an idea of how to do this properly ?

Thank you

tosscrosby
Aged Yak Warrior

676 Posts

Posted - 2009-03-30 : 10:41:59

Try:

print @sql -- this will show your select statement
exec sp_executesql @SQL --this will execute the code

You'll probably have issues trying to execute the code that you've stated in your post. But, I am assuming it's an example, as stated.

Terry

-- Procrastinate now!

the_justin
Starting Member

19 Posts

Posted - 2009-04-02 : 03:00:46

thanks for the reply,
when I print it, it gave me this:
select @cnt = 1 from blah
and if i copy and run it, it'll run fine. however, if i execute the variable string that holds it, it failed.

maeenul
Starting Member

20 Posts

Posted - 2009-04-02 : 10:35:33

This is a very advanced sql. In fact you will have to use EXECUTE sp_executesql

DECLARE @IntVariable int;
DECLARE @SQLString nvarchar(500);
DECLARE @ParmDefinition nvarchar(500);
DECLARE @max_title varchar(30);

SET @IntVariable = 197;
SET @SQLString = N'SELECT @max_titleOUT = max(Title)
FROM AdventureWorks.HumanResources.Employee
WHERE ManagerID = @level';
SET @ParmDefinition = N'@level tinyint, @max_titleOUT varchar(30) OUTPUT';

EXECUTE sp_executesql @SQLString, @ParmDefinition, @level = @IntVariable, @max_titleOUT=@max_title OUTPUT;
SELECT @max_title;

If you want to get a details of how this works you can go to the following link.
[url]http://sqlservertipsntricks.blogspot.com/2009/04/declare-intvariable-int-declare.html[/url]

-----------------------
maeenul

http://sqlservertipsntricks.blogspot.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources