Tanx Guys but this is how i solved it:
declare @sql nvarchar(max);
declare @sql2 nvarchar(max);
declare @countout int=0;
declare @Tempsql nvarchar(max);
set @sql='';
set @Tempsql='';
DECLARE @lbound int,
@ubound int
SET @lbound = 1;
SET @ubound = 100000;
set @sql='select @countout=count(*) from Trans tn '
if @FromDate is not null
begin
set @Tempsql= @Tempsql+' where tn.Date >= ''' + convert(varchar, @FromDate,20)+'''';
end
if @ToDate is not null
begin
set @ToDate= DATEADD("day", 1, @ToDate)
set @Tempsql= @Tempsql+' and tn.Date <= ''' + convert(varchar, @ToDate,20)+'''';
end
set @sql= @sql + @Tempsql;
Declare @sqlParam nvarchar(100)
SET @sqlParam = '@countOut int output';
EXEC sp_executeSQL @sql,@sqlParam,@countOut OUTPUT;
if (@countOut=0)
return 0;
EXEC dbo.Paging_Get_Bounds @countOut,@page , @pagesize, @lbound output , @ubound output ;
set @sql=' select '+CONVERT(varchar, @countOut)+' as countOut,* from (
select (ROW_NUMBER() OVER (ORDER BY tn.Date DESC) ) as rownumber,*
from Trans tn
)as tbl
where rownumber between '+CONVERT(varchar, @lbound)+' and '+CONVERT(varchar, @ubound);
EXEC sp_executeSQL @sql