get value from exec(@sql) - 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

General SQL Server Forums

New to SQL Server Programming

get value from exec(@sql)

Author

Topic

albertkohl
Aged Yak Warrior

740 Posts

Posted - 2012-04-30 : 17:26:30

okay, so i know i'm totally just not thinking of something here, but i have a stored proc i'm trying to have work with a dynamic table name. i'm trying to get how many rows are in the table, how to a capture that from the Exec(@sql) command?


declare @tablename varchar(255) = '#temp'
declare @sql varchar(max)
declare @maxid varchar(30)
set @sql = 'select max(rec_no) maxid from ' + @tablename
exec(@sql)

any help would be great! thanks in advance!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2012-04-30 : 18:50:12

Use sp_executesql to get output from dynamic SQL.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2012-04-30 : 18:50:25

Examples are in sp_executesql topic in BOL.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

vinu.vijayan
Posting Yak Master

227 Posts

Posted - 2012-05-01 : 07:00:05

Yes. You'll have to use sp_executesql. Check the link:

[url]http://hspinfo.wordpress.com/2011/05/02/how-do-i-get-result-of-dynamic-sql-into-a-variable/[/url]

N 28° 33' 11.93148"
E 77° 14' 33.66384"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources