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.3k 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

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


Categories

...