[sp_Wsi_Paging] 分页 存储过程

CREATE PROCEDURE [dbo].[sp_Wsi_Paging]
@strSQL        varchar(max) = '',        -- 如：'a.UserName,a.Password,b.id from Users a left join Roles b on a.id=b.id where a.id>0'
@strOrder    varchar(max) = '',        -- 排序的字段名 如：'a.id desc,a.AddTime desc'
@PageSize    int = 10,                -- 页尺寸
@PageIndex    int = 1                    -- 页码
AS

declare @ExecSQL   varchar(max)

IF @PageSize=-1
BEGIN
set @ExecSQL='SELECT  '+@strSQL + ' ORDER BY ' + @strOrder
END
ELSE
BEGIN
    set @ExecSQL='SELECT * FROM (SELECT ROW_NUMBER() OVER ( ORDER BY ' + @strOrder + ') AS pos,' + @strSQL + ') AS sp WHERE pos BETWEEN '+str((@PageIndex-1)*@PageSize+1)+' AND '+str(@PageIndex*@PageSize)
END
--print @ExecSQL
exec (@ExecSQL)
GO