通用存储过程

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go


CREATE  PROCEDURE [dbo].[GetDataWithPage]
	@TableName NVARCHAR(MAX),	--表名
	@Fields NVARCHAR(MAX),		--各字段
	@Where NVARCHAR(MAX),		--条件语句
	@OrderBy NVARCHAR(MAX),		--排序
	@Groupby NVARCHAR(MAX),		--归组
	@PageIndex INT,				--当前页
	@PageSize INT				--每页数量
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;							--设置NoCount
	DECLARE @BeginIndex INT					--查询开始位置
	DECLARE @EndIndex INT					--查询结束位置
	DECLARE @Sql NVARCHAR(MAX)				--要执行的SQL 语句
	DECLARE @SqlCount NVARCHAR(MAX)			--计算记录数的SQL 语句
	DECLARE @GroupbyString NVARCHAR(MAX)	--分组语句

	IF(@Where = '') BEGIN
		SET @Where = '1 = 1'
	END

	SELECT @BeginIndex = (@PageIndex - 1) * @PageSize
	SELECT @EndIndex = @PageIndex * @PageSize
	
	IF(@Groupby <> N'') BEGIN
		SET @GroupbyString = N' GROUP BY ' + @Groupby
	END ELSE BEGIN
		SET @GroupbyString = N' '
	END

	SET @Sql = N'
	BEGIN 
        WITH TheTable AS(
		    SELECT ' + @Fields + ', ROW_NUMBER() OVER(ORDER BY ' + @OrderBy + ') AS RowNumber
		    FROM ' + @TableName + ' 
            WHERE ' + @Where + ' ' + @GroupbyString + '
        )
		
        SELECT * 
        FROM TheTable 
        WHERE RowNumber > ' + CONVERT(NVARCHAR(255), @BeginIndex) + ' 
            AND RowNumber <= ' + CONVERT(NVARCHAR(255), @EndIndex) + '
	END'

	SET @SqlCount = N'
    BEGIN 
        WITH TheTable AS(
		    SELECT ROW_NUMBER() OVER(ORDER BY ' + @OrderBy + ') AS RowNumber
		    FROM ' + @TableName + ' WHERE ' + @Where + ' ' + @GroupbyString + '
        )
        
		SELECT COUNT(*) AS [Rows] FROM TheTable
	END'

PRINT @Sql
--PRINT @SqlCount
EXECUTE SP_EXECUTESQL @Sql
EXECUTE SP_EXECUTESQL @SqlCount

END


-------------------

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON