Dynamic SQL output of a query to a variable

I would like to output the result of the dynamic SQL into a variable called @Count but not sure what the syntax or even the code should like to accomplish this.

The code looks as follows:

declare @tab nvarchar(255) = 'Person.person'

declare @Count int
declare @SQL nvarchar(max) = 'select  count(*) from '+ @tab


exec(@SQl)


select @Count

thank you

Answer

You can utilize sp_executesql to execute your count() query, and output it @Count.

Try this:

-- Set the table to count from
declare @tab nvarchar(255) = 'Person.person'

-- Assign the SQL query
declare @SQL nvarchar(255) = N'SELECT count(*) FROM ' + @tab

-- Pepare for sp_executesql
declare @Count int
declare @Params nvarchar(100) = N'@Count int output'

-- Set the count to @Count
exec sp_executesql @SQL, @Params, @Count=@Count output

-- Output @Count
select @Count

One last thing: Person.person looks like you might be trying to reference a person column from a Person table. But the above query is a literal representation of what it looks like you’re trying to achieve in your question.