EXEC result in variable - SQL Server Forums

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 2005 Forums

Transact-SQL (2005)

EXEC result in variable

Author

Topic

nice123ej
Starting Member

48 Posts

Posted - 2007-07-07 : 01:47:47

I want to get a singular result from EXEC function

example
-------------------------------------------
declare @sqlscript varchar(1000)
declare @i int

set @sqlscript = 'select count(*) from mytable'

set @i = exec(sqlscript)
-------------------------------------------

i know this script wont work but how can i get the count result in @i

i know i can use select @i= count(*) from mytable
but this is not what i want @sqlscript is variable and can be anything

can anyone help?!

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-07-07 : 03:06:01

you have to use the sp_executesql.

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

KH
[spoiler]Time is always against us[/spoiler]