Back

Explore Courses Blog Tutorials Interview Questions
0 votes
1 view
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?

1 Answer

0 votes
by (40.4k 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, @[email protected] 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.

Welcome to Intellipaat Community. Get your technical queries answered by top developers!

28.4k questions

29.7k answers

500 comments

94.1k users

Browse Categories

...