Getting result of dynamic SQL into a variable for sql-server

Question

Executing dynamic SQL as follows in the Stored Procedure:

DECLARE @sqlCommand nvarchar(1000)

DECLARE @city varchar(75)

SET @city = 'London'

SET @sqlCommand = 'SELECT COUNT(*) FROM customers WHERE City = @city'

EXECUTE sp_executesql @sqlCommand, N'@city nvarchar(75)', @city = @city

How do I use the count(*) column value as a return value in the SP?

Answer 1

Try using the below code:

DECLARE @sqlCommand nvarchar(1000)

DECLARE @city varchar(75)

declare @counts int

SET @city = 'New York'

SET @sqlCommand = 'SELECT @cnt=COUNT(*) FROM customers WHERE City = @city'

EXECUTE sp_executesql @sqlCommand, N'@city nvarchar(75),@cnt int OUTPUT', @city = @city, @cnt=@counts OUTPUT

select @counts as Counts

Enroll yourself in the SQL server certification to learn in-depth about SQL statements, queries and become proficient in SQL.