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....
Loading
Leon PuthPosted May 23, 2014, 6:18 AM
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 ValechaPosted May 23, 2014, 5:52 AM
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 PuthPosted May 23, 2014, 3:25 AM
exec sp_executesql 'select @Totalrecords =count(*) from Table ' + AddYourDynamicFilterHere, '@Totalrecords int output', @Totalrecords output;