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