how to pass value from dynamic sql to a 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

SQL Server 2000 Forums

SQL Server Development (2000)

how to pass value from dynamic sql to a variable

Author

Topic

DURGESH
Posting Yak Master

105 Posts

Posted - 2008-06-25 : 01:54:51

hello all
good morning

i am executing a select query using dynamic sql
example @sql='select @var=emp from employee where id='''+@id+''''
execute(@sql)
now i want to store the value of @var into @var2

if i am assigning @var2=@var then it is returning null

can anybody help me to solve this issue

thanks in advance

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-25 : 02:03:19

You need to use sp_executesql for this. Make the variable @var as of type OUTPUT and you can get its value outside.

http://www.sommarskog.se/dynamic_sql.html

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-06-25 : 10:00:31

quote:
Originally posted by DURGESH

hello all
good morning

i am executing a select query using dynamic sql
example @sql='select @var=emp from employee where id='''+@id+''''
execute(@sql)
now i want to store the value of @var into @var2

if i am assigning @var2=@var then it is returning null

can anybody help me to solve this issue

thanks in advance

When you dont pass object names as parameters, you dont need to use dynamic sql at least in the example you have given

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources