-
set ANSI_NULLS ON
-
set QUOTED_IDENTIFIER ON
-
go
-
-- ==================================================
-
-- Author: Aaron Longnion
-
-- Create date: 10/18/2007
-
-- Description: A gereral-use way of dynamically
-
-- creating SQL to easily paginate the results;
-
-- also includes a total record count based on
-
-- the search criteria in the WHERE clause
-
-- ==================================================
-
ALTER PROCEDURE [dbo].[sproc_pagination]
-
-- Add the parameters for the stored procedure here
-
@SqlColumns VARCHAR(MAX),
-
@SqlFriendlyColumns VARCHAR(MAX),
-
@SqlTableClause VARCHAR(MAX),
-
@StartRow INT,
-
@EndRow INT,
-
@SqlWhere VARCHAR(MAX),
-
@SqlRowNumOrderBy VARCHAR(MAX),
-
@SqlOuterOrderBy VARCHAR(MAX)
-
AS
-
DECLARE @rsSQL NVARCHAR(MAX)
-
DECLARE @rcSQL NVARCHAR(MAX)
-
BEGIN
-
-- SET NOCOUNT ON added to prevent extra result sets from
-
-- interfering with SELECT statements.
-
SET NOCOUNT ON;
-
-- build pagination SQL, using StartRow and EndRow to determine
-
-- which results to output
-
SET @rsSQL = N' WITH tempTable AS ( ' +
-
N' SELECT ' +
-
@SqlColumns +
-
N' , ROW_NUMBER() OVER(ORDER BY ' +
-
@SqlRowNumOrderBy +
-
N' ) AS RowNumber ' +
-
N' FROM ' +
-
@SqlTableClause
-
IF @SqlWhere + '' <> ''
-
BEGIN
-
SET @rsSQL = @rsSQL +
-
N' WHERE ' +
-
@SqlWhere
-
END
-
SET @rsSQL = @rsSQL +
-
N' ) SELECT ' +
-
@SqlFriendlyColumns +
-
N' FROM tempTable ' +
-
N' WHERE RowNumber >= ' +
-
CAST(@StartRow AS NVARCHAR(32)) +
-
N' AND RowNumber <= ' +
-
CAST(@EndRow AS NVARCHAR(32)) +
-
N' ORDER BY ' +
-
@SqlOuterOrderBy
-
-
-- uncomment PRINT to debug
-
--PRINT @rsSQL
-
EXEC sp_executesql @rsSQL
-
-- build second recordset simple for the count
-
SET @rcSQL =
-
N'SELECT COUNT(*) AS CountAll FROM ' +
-
@SqlTableClause
-
IF @SqlWhere + '' <> ''
-
BEGIN
-
SET @rcSQL = @rcSQL +
-
N' WHERE ' +
-
@SqlWhere
-
END
-
-
EXEC sp_executesql @rcSQL
-
SET NOCOUNT OFF;
-
END
