Intellipaat Back

Explore Courses Blog Tutorials Interview Questions
0 votes
2 views
in SQL by (20.3k points)

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?

2 Answers

0 votes
by (40.7k points)

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.

0 votes
by (1.5k points)

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

31k questions

32.8k answers

501 comments

693 users

Browse Categories

...