Output from dynamic sql

Question

Output from dynamic sql

Jeremy Kemp

SSCertifiable

Points: 6754
More actions
July 10, 2003 at 4:13 am

#82716

Recently, someone posted a way of getting a returned value from dynamic sql but I cannot find it.
For example, I want the number of rows in a table where there table is a variable:
declare @sqlstring nvarchar(1000), @table_name varchar(25)
set @table_name = 'tableA'
set @sqlstring = 'select count(*) from ' + @table_name
exec sp_executesql @sqlstring /* what comes next? */
Thanks
Jeremy

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

mccork SSC-Insane Points: 22288 More actions · Answer 1

Here's an example:

declare @sqlstring nvarchar(1000), @table_name varchar(25)

declare @result int



set @table_name = 'pubs.dbo.authors'

set @sqlstring = 'select @c = count(*) from ' + @table_name



exec sp_executesql @sqlstring, N'@c INT OUTPUT', @result OUTPUT



select @result

Cheers,
- Mark

Jeremy Kemp SSCertifiable Points: 6754 More actions · Answer 2

Jeremy Kemp

SSCertifiable

Points: 6754

July 10, 2003 at 5:15 am

#464794

Thanks a lot.

Jeremy

Antares686 SSC Guru Points: 125444 More actions · Answer 3

Try this



declare @sqlstring nvarchar(1000), @table_name varchar(25), @cntout as int



set @table_name = 'tableA'

set @sqlstring = 'select @cntout = count(*) from ' + @table_name



exec sp_executesql @sqlstring, N'@cntout as int OUTPUT', @cntout = @cntout OUTPUT



PRINT @cntout

rstone SSCertifiable Points: 6209 More actions · Answer 4

You might try bol (bools online) for the topics "sp_executesql" and "Using sp_executesql". The object (e.g., table) names need to be fully qualified in order for SQL Server to reuse a cached execution plan.

Also, the execution of the query is very much like (maybe it's the same except for permissions) as calling a nested stored procedure. It inherits connection properties, but setting connection properties in the query are temporary. The query also has access to temporary tables and cursors declared in the calling batch. If the calling batch is a stored procedure, permission to the procedure does not translate to permission to execute the ad hoc query. The user would need permission to execute the proc plus permission to do what’s in the query executed by the proc. (Imagine having an ad hoc query string passed to the procedure that drops databases.)

The batch executing the query (also a batch) can trap or handle syntax errors if they are returned by the execution of the query. If you ignore them, the original batch will continue executing any statements following.

RandyHelpdesk: Perhaps Im not the only one that does not know what you are doing. 😉