Return count of records into a variable using sp_executesql

See more:

SQL

SQL-Server

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.