分页存储过程

-- ================================================
-- Template generated from Template Explorer using:
-- Create Procedure (New Menu).SQL
--
-- Use the Specify Values for Template Parameters 
-- command (Ctrl-Shift-M) to fill in the parameter 
-- values below.
--
-- This block of comments will not be included in
-- the definition of the procedure.
-- ================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[OrderInfo_PAGE]
    @TableName VARCHAR(50),  --表名
    @ReFieldsStr VARCHAR(200) = '*', --字段名(全部字段为*)
    @OrderString VARCHAR(200),  --排序字段(必须!支持多字段不用加order by)
     @WhereString VARCHAR(500) =N'', --条件语句(不用加where)
     @PageSize INT,   --每页多少条记录
     @PageIndex INT = 1 ,  --指定当前为第几页
     @TotalRecord INT OUTPUT  --返回总记录数
AS
BEGIN
     --处理开始点和结束点
     DECLARE @StartRecord INT;
    DECLARE @EndRecord INT; 
     DECLARE @TotalCountSql NVARCHAR(500); 
     DECLARE @SqlString NVARCHAR(2000);  
    SET @StartRecord = (@PageIndex-1)*@PageSize + 1--起始记录
     SET @EndRecord = @StartRecord + @PageSize - 1 --结尾记录
     SET @TotalCountSql= N'select @TotalRecord = count(*) from ' + @TableName;--总记录数语句
     SET @SqlString = N'(select row_number() over (order by '+ @OrderString +') as rowId,'+@ReFieldsStr+' from '+ @TableName;--查询语句
     --
     IF (@WhereString! = '' or @WhereString!=null)
            BEGIN
            SET @TotalCountSql=@TotalCountSql + '  where '+ @WhereString;
            SET @SqlString =@SqlString+ '  where '+ @WhereString;  
        END
    --第一次执行得到
    EXEC sp_executesql @totalCountSql,N'@TotalRecord int out',@TotalRecord OUTPUT;--返回总记录数
    ----执行主语句
    SET @SqlString ='select * from ' + @SqlString + ') as t where rowId between ' + ltrim(str(@StartRecord)) + ' and ' +  ltrim(str(@EndRecord));
    Exec(@SqlString)  
END

 


---执行存储过程
USE [Zx_Test]
GO

DECLARE	@return_value int,
		@TotalRecord int

EXEC	@return_value = [dbo].[OrderInfo_PAGE]
		@TableName = N'[Zx_Test].[dbo].[Table_1]',
		@ReFieldsStr = N'*',
		@OrderString = N'Id',
		@PageSize = 2,
		@PageIndex = 2,
		@TotalRecord = @TotalRecord OUTPUT

SELECT	@TotalRecord as N'@TotalRecord'

SELECT	'Return Value' = @return_value

GO