Create PROCEDURE [dbo].[Table_GetList]
@WhereStr nvarchar(256) = '', -- 查询条件(注意: 不要加 WHERE)
@OrderStr nvarchar(128) = '', -- 设置排序
@PageIndex int = 1, -- 页尺寸
@PageSize int = 0 , -- 页码
@RecordCount int output -- 总的记录数
AS
DECLARE @TableName nvarchar(32) -- 表名
DECLARE @PriKeyName nvarchar(32) -- 主键列或标示列
DECLARE @FldNames nvarchar(512) -- 字段名,多个字段通过逗号分割
DECLARE @StrByPage nvarchar(1024) -- 分页查询语句
DECLARE @StrNoPage nvarchar(512) -- 不分页查询语句，获得所有符合条件的记录
DECLARE @StrTotal nvarchar(512) -- 统计符合条件的纪录数
DECLARE @StrNonResult nvarchar(512) -- 返回空记录的语句
DECLARE @StrTmp nvarchar(128)
DECLARE @StrOrder nvarchar(128)
DECLARE @TotalPage int
SET @TableName = 'ProductActions as b'
SET @PriKeyName = 'ActionID'
SET @FldNames = 'b.*'
SET @TotalPage = 0
--不传值默认主键降序
IF @OrderStr is null or @OrderStr=''
SET @OrderStr = ''
IF @OrderStr = '' -- 升序
BEGIN
SET @StrTmp = @PriKeyName + '>(SELECT max([' + @PriKeyName + ']) FROM '
SET @StrOrder = ' order by ' + @PriKeyName + ' asc'
END
ELSE IF @OrderStr = '' -- 降序
BEGIN
SET @StrTmp = @PriKeyName + '<(SELECT min([' + @PriKeyName + ']) FROM '
SET @StrOrder = ' order by ' + @PriKeyName + ' desc'
END
ELSE -- 用户自定义排序规则
BEGIN
SET @StrTmp = ''
SET @StrOrder = ' order by ' + @OrderStr
END
SET @StrNonResult = 'SELECT ' + @FldNames + ' FROM ' + @TableName + ' WHERE 1=2'
IF @WhereStr is null or @WhereStr = '' -- 如果没有额外的查询条件
BEGIN
SET @StrTotal = N'SELECT @RecordCount = count(1) FROM ' + @TableName
SET @StrNoPage = N'SELECT ' + @FldNames + ' FROM ' + @TableName + @StrOrder
END
ELSE
BEGIN
SET @StrTotal = N'SELECT @RecordCount = count(1) FROM ' + @TableName + ' WHERE ' + @WhereStr
SET @StrNoPage = N'SELECT ' + @FldNames + ' FROM ' + @TableName + ' WHERE ' + @WhereStr + @StrOrder
END
-- 取得所有符合查询条件的记录数
EXEC sp_EXECuteSql @StrTotal,N'@RecordCount int output',@RecordCount output
-- 如果没有适合条件的记录时,提供一个空的记录集并退出查询
IF @RecordCount = 0
BEGIN
SET @TotalPage = 0
EXEC sp_EXECuteSql @StrNonResult
RETURN 0
END
-- 执行查询，此时记录集不为空
IF @PageSize = 0 -- 当不需要分页时
BEGIN
SET NOCOUNT ON
SET @TotalPage = 0
EXEC sp_EXECuteSql @StrNoPage
RETURN
END
ELSE -- 当需要分页时
BEGIN
SET NOCOUNT ON
-- 得到记录的页数，并调整页号，分页从1开始
SET @TotalPage=CEILING(cast(@RecordCount as float)/@PageSize)
IF(@PageIndex>@TotalPage)
SET @PageIndex=@TotalPage
IF(@PageIndex <1)
SET @PageIndex=1
IF @PageIndex =1 -- 如果是第一页
BEGIN
IF @WhereStr=''
SET @StrByPage = N'SELECT TOP ' + cast(@PageSize as varchar) + ' ' + @FldNames + ' FROM ' + @TableName + @StrOrder
ELSE
SET @StrByPage = N'SELECT TOP ' + cast(@PageSize as varchar) + ' ' + @FldNames + ' FROM ' + @TableName + ' WHERE ' + @WhereStr + @StrOrder
END
ELSE -- 以后页
BEGIN
IF (@OrderStr='' or @OrderStr='') -- 按主键升序或降序
BEGIN
IF @WhereStr=''
SET @StrByPage = N'SELECT TOP ' + cast(@PageSize as varchar) + ' ' + @FldNames
+ ' FROM ' + @TableName
+ ' WHERE ' + @StrTmp
+ ' (SELECT TOP ' + cast((@PageIndex-1) * @PageSize as varchar) + ' ' + @PriKeyName
+ ' FROM ' + @TableName + @StrOrder + ') as tmptbl)'
+ @StrOrder
ELSE
SET @StrByPage = N'SELECT TOP ' + cast(@PageSize as varchar) + ' ' + @FldNames
+ ' FROM ' + @TableName
+ ' WHERE ' + @StrTmp
+ ' (SELECT TOP '+ cast((@PageIndex-1) * @PageSize as varchar) + ' ' + @PriKeyName
+ ' FROM ' + @TableName + ' WHERE ' + @WhereStr + @StrOrder + ') as tmptbl)'
+ ' and ' + @WhereStr
+ @StrOrder
END
ELSE -- 没有排序规则或者用户自定义规则
BEGIN
IF @WhereStr=''
SET @StrByPage = N'WITH cte AS('
+ ' SELECT ROW_NUMBER() OVER('+@StrOrder+') AS ROWNUMBER ,' + @FldNames
+ ' FROM '+ @TableName
+ ') SELECT * FROM cte WHERE ROWNUMBER BETWEEN '
+ CAST(((@PageIndex -1) * @PageSize +1) AS varchar) + ' AND '
+ CAST(@PageIndex * @PageSize AS varchar)
ELSE
SET @StrByPage = N'WITH cte AS('
+ ' SELECT ROW_NUMBER() OVER('+@StrOrder+') AS ROWNUMBER ,' + @FldNames
+ ' FROM '+ @TableName + ' WHERE ' + @WhereStr
+') SELECT * FROM cte WHERE ROWNUMBER BETWEEN '
+ CAST(((@PageIndex -1) * @PageSize +1) AS varchar) + ' AND '
+ CAST(@PageIndex * @PageSize AS varchar)
END
END
END
--PRINT @StrByPage
EXEC sp_executeSql @StrByPage
RETURN