Click here to Skip to main content
15,039,149 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I set about doing something simple and it is baffling. Why is FilteredRowCount NULL?

In the code below, "Count" is 2210 (total records in the table). But "FilteredRowCount" is NULL. How can I get the count into a local variable using sp_executesql? I need to do it this way because I need to add a highly dynamic WHERE clause.

Thanks in advance!

Here is the code:

DECLARE @MyCount INT
DECLARE @Params nvarchar(1000)
DECLARE @MySql as nVARCHAR(max)

SET @Params = N'@Count int OUT'
SET @MySql =N'Select count(*) as Count from Accounts'

Execute sp_Executesql @MySql, @Params, @Count=@MyCount OUT

select @MyCount as FilteredRowCount

Count
2210

FilteredRowCount
NULL

What I have tried:

I tried just doing a "SELECT @Count" and tossed our FilteredRowCount. I played with doind a select from the select. I can make this work without execute_sql simply by saying

select count(*) as Count
select @Count
--This works. But I need to append a WHERE clause that could be many different things, so I can't enter the whole query as a literal which seems to be the only thing that works.
Posted
Updated 8-Jan-21 11:00am

1 solution

Instead of
SQL
SET @MySql =N'Select count(*) as Count from Accounts'
try
SQL
SET @MySql =N'Select @count = count(*) from Accounts'
You have to assign the result to the output variable within the dynamic sql
   
Comments
JPatSoCal 9-Jan-21 16:43pm
   
Seemed like a good idea but now I get "Must declare the scalar variable "@MyCount". But it is declared. If I drop the @, it executes but I still cannot access the value.

DECLARE @MyCount INT
DECLARE @MySql as nVARCHAR(max)

SET @MySql =N'Select @MyCount=count(*) from Accounts'

Execute sp_Executesql @MySql
CHill60 11-Jan-21 4:01am
   
You have put @MyCount into the dynamic sql string. Look at my solution again
SET @MySql =N'Select @count = count(*) from Accounts'

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900