我在SQL server 2005数据库中创建了以下用于常规分页的存储过程:
USE [training]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[usp_Pagination1]
@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;
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
PRINT @rsSQL
EXEC sp_executesql @rsSQL
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
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
当我为我的表vpCategory执行它时,我得到一条消息:
消息209,16级,状态1,行1
但在结果选项卡中,它给了我正确的输出。
“消息”选项卡还提供以下查询,这些查询是由过程生成的:
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
有人能帮我找出存储过程出了什么问题吗?