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.

Answer 2

CREATE PROCEDURE GetCustomerCountByCity

    @City nvarchar(75),

    @Count int OUTPUT

AS

BEGIN

    DECLARE @sqlCommand nvarchar(1000);

    SET @sqlCommand = N'SELECT @Count = COUNT(*) FROM customers WHERE City = @City';

    EXECUTE sp_executesql @sqlCommand, N'@City nvarchar(75), @Count int OUTPUT',

                          @City = @City, @Count = @Count OUTPUT;

END

Using Above query to get respective output