Need help for general sql stored procedure for pagination

Question

Hi,

I've created following stored procedure in my SQL server 2005 database for general pagination:

USE [training]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[usp_Pagination1]  
    -- Add the parameters for the stored procedure here 
    @SqlColumns VARCHAR(MAX), 
    @SqlFriendlyColumns VARCHAR(MAX), 
    @SqlTableClause VARCHAR(MAX), 
    @StartRow INT, 
    @EndRow INT, 
    @SqlWhere VARCHAR(MAX), 
    @SqlOuterWhere 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; 
/* 
sample dynamically created SQL: 
    WITH PersonContact AS 
    ( 
        SELECT PC.FirstName, PC.LastName, PC.EmailAddress, 
        ROW_NUMBER() OVER(ORDER BY PC.ContactID) AS RowNumber 
        FROM Person.Contact PC 
    ) 
    SELECT FirstName, LastName, EmailAddress 
    FROM PersonContact 
    WHERE RowNumber > @StartRow AND RowNumber < @EndRow 
    ORDER BY LastName DESC, EmailAddress 
*/ 
    -- 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 @SqlOuterWhere + '' <> '' 
        BEGIN 
            SET @rcSQL = @rcSQL +                   
                N' WHERE ' +  
                      @SqlOuterWhere
        END 

    EXEC sp_executesql  @rcSQL     
    SET NOCOUNT OFF; 
END

Executed procedure with my parameters:

USE [training]
GO

EXEC    [dbo].[usp_Pagination1]
        @SqlColumns = N'tab1.categoryId,tab1.categoryName,tab1.description,tab1.parentCategory,tab2.categoryName AS parentCategoryName',
        @SqlFriendlyColumns = N'categoryId,categoryName,description,parentCategory,parentCategoryName',
        @SqlTableClause = N'vpCategory tab1 LEFT JOIN vpCategory tab2 ON tab1.parentCategory = tab2.categoryId',
        @StartRow = 1,
        @EndRow = 1,
        @SqlWhere = N'tab1.categoryId = 1',
        @SqlOuterWhere = N'categoryId = 1',
        @SqlRowNumOrderBy = N'tab1.categoryId',
        @SqlOuterOrderBy = N'categoryId'

GO

When I execute it for my table vpCategory, I get message : Msg 209, Level 16, State 1, Line 1 Ambiguous column name 'categoryId'.

But in result tab it gives me correct output.

ALso message tab gives me following query, that is being built by procedure:

WITH tempTable AS (  SELECT tab1.categoryId,tab1.categoryName,tab1.description,tab1.parentCategory,tab2.categoryName AS parentCategoryName , ROW_NUMBER() OVER(ORDER BY tab1.categoryId ) AS RowNumber  FROM vpCategory tab1 LEFT JOIN vpCategory tab2 ON tab1.parentCategory = tab2.categoryId WHERE tab1.categoryId = 1 ) SELECT categoryId,categoryName,description,parentCategory,parentCategoryName FROM tempTable  WHERE RowNumber >= 1 AND RowNumber <= 1 ORDER BY categoryId

When I execute the above query in new window, it gives me no error!

Can anyone help me out what is going wrong with the stored procedure?

Answer 1

Got It!

Actually we dot require outer where codition.

It is:

 ALTER PROCEDURE [dbo].[usp_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; 
/* 
sample dynamically created SQL: 
    WITH PersonContact AS 
    ( 
        SELECT PC.FirstName, PC.LastName, PC.EmailAddress, 
        ROW_NUMBER() OVER(ORDER BY PC.ContactID) AS RowNumber 
        FROM Person.Contact PC 
    ) 
    SELECT FirstName, LastName, EmailAddress 
    FROM PersonContact 
    WHERE RowNumber > @StartRow AND RowNumber < @EndRow 
    ORDER BY LastName DESC, EmailAddress 
*/ 
    -- 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 

Answer 2

Looks like your second sp_executesql could be generating this error -the @SqlOuterWhere should use an alias on the categoryid column.