Dynamic generated SQL in Stored Procedure for paging with Ext.Net GridPanel

This Blog moved to http://webapps-in-action.com/

Basically as the title says. Just another stored procedure like in my previous post: Ext.NET GridPanel: Paging against large tables using Linq-To-SQL with Stored Procedures. Kind of part II.

The Difference: Two more parameters @WhereString and @SortString to pass on "WHERE xyz and ..." and "ORDER BY xyz, ..." to the stored procedure.

It's quite obvious, that the filtering needs to be done on MSSQL, when you only get
paged results and want to have valid total count and resultset.

This could might be a solution for you:

ALTER PROCEDURE [dbo].[sp_paging_dynamic]

@PageStart int,
@PageLimit int,
@WhereString nvarchar(max),
@SortString nvarchar(max),
@TotalCount int output

AS
BEGIN
 DECLARE @SQLCommand nvarchar(max)
 DECLARE @ParmDefinition nvarchar(500)
 SET @ParmDefinition=N'@TotalCount int OUTPUT'
 SET @SQLCommand =  
  
  'SELECT @TotalCount = count(*) FROM ' + 
  'dbo.TABLE '+@WhereString+';

   WITH ResultSet AS ' + 
   '(SELECT row_number() over(order by ' + @SortString + ') AS RowNumber, ' + 
   ' * FROM dbo.TABLE ' + @WhereString + ')
   SELECT * FROM ResultSet WHERE RowNumber BETWEEN ' + cast(@PageStart as varchar) + ' AND ' + cast(@PageLimit as varchar)

 EXEC sp_executesql @SQLCommand, @ParmDefinition, @TotalCount=@TotalCount OUTPUT

 RETURN @TotalCount
END