最新SqlServer2005分页存储过程备份

Create PROCEDURE [dbo].[USP_Execute_Page]
(
???? @Fields nvarchar(max) = 'T.*'????? --字段
???,@TableName nvarchar(200)?????????? --表名
???,@Joins nvarchar(max) = ''????????? --连接表及规则
???,@Wheres nvarchar(max) = ''???????? --不用 Where 关键字
???,@Orders nvarchar(600) = ''???????? --不同 Order By 关键字
???,@PageSize int = 20???????????????? --页宽
???,@PageIndex int = 0???????????????? --1.表示第一页? |? 0.表示不分页?
???,@RowCount int output?????????????? --记录总数，只在当前页小于1时产生
)
AS
SET NOCOUNT ON

-- 设置where的默认值
if LTRIM(RTRIM(@Wheres)) = ''
??? begin set @Wheres = ''? end
else
??? begin set @Wheres = ' Where ' + @Wheres end?

--设置Order By的默认值
if LTRIM(RTRIM(@Orders)) = ''
??? begin set @Orders = '' end
else
??? begin set @Orders = ' Order By ' + @Orders end

----------------- 定义变量 -----------------//
declare @strSql nvarchar(max)
declare @iSum int
declare @defaultSql nvarchar(max)
set @defaultSql = 'With S1 AS (Select '+ @Fields + ' From ' + @TableName + ' AS T ' + @Joins +') ';
??????????????????
-- 计算记录总数 --
IF @PageIndex = 1 BEGIN
??? set @strSql = @defaultSql + 'Select @iSum = count(0) From S1 ' + @Wheres;
??????????????????
??? exec sp_executeSQL @strSql
???????,@params = N'@iSum int output'
???????,@iSum = @iSum OUTPUT ;
??? set @RowCount = @iSum;
END

--------------- 不执行分页查询 ---------------//
IF @pageIndex < 1
BEGIN
??? SET @strSql = @defaultSql + 'Select Top ' + Convert(varchar(10),@PageSize) + ' * From S1 ' + @Wheres + ' ' + @Orders;
??? EXEC(@strSql) ;
END

--------------- 执行分页查询 -----------------//ELSE? BEGIN? ??? DECLARE @min int,@max int; ??? SET @min = (@pageIndex-1) * @pageSize; ??? SET @max = (@pageIndex) * @PageSize;?????? ??? ??? SET @strSql = @defaultSql + ',????????????????? S2 AS(???????????????????? Select Row_Number() Over(' +@Orders+ ') AS RowId,*? From S1 ' +@Wheres+ '? ????????????????? )????????????????? Select * From S2 Where RowId > ' + CONVERT(varchar(10),@min) + '? ????????????????? AND RowId <= ' + CONVERT(varchar(10),@max)??? ??? EXEC (@strSql) END

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!