It took me a while to figure this out so I’m going to blog about this. Note that this article is only for SQL Server users.
This code snippet below is not very useful if you just look at it without understanding what I used it for; so here is some background.
I have a big stored procedure, RefreshTables, which runs 15 other stored procedures to populate data and they are wrapped by a transaction in RefreshTables SP. I also have a ExecuteDataPopulation SP which calls the 15 stored procedures and log the result for the execution. Therefore, if one out of the 15 procedure failed, all of the affected data is rolled back. At the same time, I would like to populate the before row count and after row count for each tables that I execute data population stored procedure on. Therefore, I can’t do any DDL during the big stored procedure (so nothing is committed) and I will have table name changing on each individual data population process. See the code below, it takes a dynamic sql (table name changing) and returns the value that dynamic sql generated:
declare @rowcount int, @sql nvarchar(1000), @tablename nvarchar(100)
set @tablename = 'users'
set @sql='select @rc = count(*) from '+@tablename
exec sp_executesql @sql, N'@rc int output',@rowcount output
select 'give me value back!', @rowcount
By using this in my Audit procedure ExecuteDataPopulation, I now can get the before and after row count! I know there is an easier way to get row count using:
create table #tmp (rowcount int);
insert into #tmp
exec('select count(*) from users');
However I cannot use it like this since I do not want to have any DDL happen during my transaction, it commits everything which happened before the DDL!!!
Hope this will be helpful to someone who is trying to figure out how to get return value out of your dynamic sql.
