July 10, 2003 at 4:13 am
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
July 10, 2003 at 5:12 am
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
July 10, 2003 at 5:15 am
Thanks a lot.
Jeremy
July 10, 2003 at 5:15 am
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
July 11, 2003 at 10:44 am
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. 😉
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply