Riddhi Valecha

Riddhi Valecha

  • 423
  • 3.3k
  • 410.6k

Count(*) for Dynamic Search Query

May 23 2014 2:57 AM
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....

Answers (3)