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