Count(*) for Dynamic Search Query

Question

Hi All....
Please help me out in dynamic search query in sql stored procedure....
----
My procedure is as -
@CompanyCode varchar(max) = null,
@Document varchar(max) = null,
@Status varchar(max) = null,
@Location varchar(max) = null

declare @SQL as nvarchar(max)

set @SQL = 'select * from Table where 1=1'

if @CompanyCode is not null
set @SQL = @SQL + 'and Table.CompanyCode = '+@CompanyCode

if @Document is not null
set @SQL = @SQL + 'and Table.Document = '+@Document

Same way, for the rest of the parameters.
-----------
Now, my query is, how do we get the total records from this query ??

i.e.
select @Totalrecords = count(*) from Table + where clause.

There are 50000 records in the table, but only 10000 satisfy the conditions.

SO, I want 10000 count as a output variable.

How do I get this??
PLease help....

Leon Puth · Answer

Riddhi You can use @@ROWCOUNT to get the row count of the last sql transaction. just read its value for instance : exec(@SQL) select @Totalrecords = @@ROWCOUNT ALTERNATIVELY if you want the result set AND the record count of the query in @sql and dont want to use the @@ROWCOUnt above then you may execute it once for the result then modify it slightly to get the count. the example below i did not check for syntax errors but it should give you an idea of how to get the data and the record count @CompanyCode varchar(max) = null @Document varchar(max) = null @Status varchar(max) = null @Location varchar(max) = null declare @SQL as nvarchar(max) declare @filterSQL as nvarchar(max) declare @countSQL as nvarchar(max) set @SQL = 'select * from Table' set @countSQL = 'select @Totalrecords =count(*) from Table ' set @filterSQL = ' where 1=1' if @CompanyCode is not null set @ filterSQL = @ filterSQL + ' and Table.CompanyCode = '+@CompanyCode if @Document is not null set @ filterSQL = @ filterSQL + ' and Table.Document = '+@Document --this returns the result of your filtered select exec(@SQL + @filterSQL) --this returns into @Totalrecords the record count of the exact same filtered select exec sp_executesql @ countSQL + @ filterSQL  ' @ Totalrecords int output'  @ Totalrecords output ;

Riddhi Valecha · Answer

Hi ... In my procedure there is no where clause.... I have specified it in the query variable i.e. @SQL That is because.... there is only 1 statement in all the If Conditions..... Do you have any other solution to this ?? Or please guide me if I am wrong... Thanks a lot ... in advance...

Leon Puth · Answer

so you want the dynamic query to be executed and return into @Totalrecords the record count? exec sp_executesql 'select @Totalrecords =count(*) from Table ' + AddYourDynamicFilterHere  ' @ Totalrecords int output'  @ Totalrecords output ;