Assign Result of Dynamic SQL to a Variable in SQL Server

Suppose you have dynamic sql that returns a single value and you want to copy it to a variable. For eg: you want the total count of the table copied to a variable. This is possible in SQL Server using the following methods:

Method 1 : Use sp_executesql system stored procedure

declare @counting int
execute sp_executesql
          N'select @count=count(*) from sys.objects',
          N'@count int output',
          @count =@counting output;
select @counting as counting

In the above method, count is assigned to the variable @count which outputs to @counting

Method 2 : Use table variable

declare @t table(counting int)
declare @counting int
insert into @t
exec('select count(*) from sys.objects')
select @counting=counting from @t
select @counting as counting

The above t-sql code copiesthe  resultset to table variable @t and the count is copied to variable @counting

OUTPUT

About The Author

Madhivanan,an MSc computer Science graduate from Chennai-India, works as a works as a Lead Subject Matter Expert at a company that simplifies BIG data. He started his career as a developer working with Visual Basic 6.0, SQL Server 2000 and Crystal Report 8. As years went by, he started working more on writing queries in SQL Server. He now has good level of knowledge in SQLServer, Oracle, MySQL and PostgreSQL as well. He is also one of the leading posters at www.sqlteam.com and a moderator at www.sql-server-performance.com. His T-sql blog is at http://beyondrelational.com/blogs/madhivanan