一。带输出参数的分页存储过程
--创建存储过程
CREATE PROCEDURE [dbo].[bin_GetPageOfRecordsCount]
@TableName varchar(32), -- 表名
@Fields varchar(1024), -- 字段
@OrderName varchar(32), -- 排序字段
@OrderType bit, -- 排序类型(非0则降序)
@PageIndex int, -- 页索引
@PageSize int, -- 页大小
@Condition nvarchar(1024), -- 查询条件(不要加where)
@Count int output
AS
BEGIN
declare @strSQL nvarchar(2048) -- 主语句
declare @strOrder nvarchar(64) -- 排序语句
if @Condition!=''
SET @strSQL = 'SELECT @Count = COUNT(*) FROM '+@TableName+' WHERE '+@Condition
else
SET @strSQL = 'SELECT @Count = COUNT(*) FROM '+@TableName
EXECUTE sp_executesql @strSQL,N'@Count INT OUTPUT',@Count OUTPUT
if @OrderType!=0
set @strOrder = ' ORDER BY '+@OrderName+' DESC'
else
set @strOrder = ' ORDER BY '+@OrderName+' ASC'
if @PageIndex=1 --第一页执行以下代码速度快
begin
if @Condition!=''
set @strSQL = 'SELECT TOP '+str(@PageSize)+ ' '+@Fields+ ' FROM '+@TableName+' WHERE '+@Condition+' '+@strOrder
else
set @strSQL = 'SELECT TOP '+str(@PageSize)+ ' '+@Fields+ ' FROM '+@TableName+' '+@strOrder
end
else
begin
if @Condition!=''
set @strSQL = 'SELECT '+@Fields+' FROM
(
SELECT ROW_NUMBER() OVER('+@strOrder+') AS Rank,'+@Fields+' FROM '+@TableName+' WHERE '+@Condition+'
) AS TEMP
WHERE Rank BETWEEN '+str((@PageIndex-1)*@PageSize+1)+' AND '+str(@PageIndex*@PageSize)
else
set @strSQL = 'SELECT '+@Fields+' FROM
(
SELECT ROW_NUMBER() OVER('+@strOrder+') AS Rank,'+@Fields+' FROM '+@TableName+'
) AS TEMP
WHERE Rank BETWEEN '+str((@PageIndex-1)*@PageSize+1)+' AND '+str(@PageIndex*@PageSize)
end
exec (@strSQL)
END
二，不带输出参数的存储过程
CREATE PROCEDURE [dbo].[bin_GetPageOfRecords]
@pageSize int = 20, --分页大小
@currentPage int , --第几页
@columns varchar(1000) = '*', --需要得到的字段
@tableName varchar(100), --需要查询的表
@condition varchar(1000) = '', --查询条件, 不用加where关键字
@ascColumn varchar(100) = '', --排序的字段名 (即 order by column asc/desc)
@bitOrderType bit = 0, --排序的类型 (0为升序,1为降序)
@pkColumn varchar(50) = '' --主键名称
AS
BEGIN --存储过程开始
DECLARE @strTemp varchar(300)
DECLARE @strSql varchar(5000) --该存储过程最后执行的语句
DECLARE @strOrderType varchar(1000) --排序类型语句 (order by column asc或者order by column desc)
BEGIN
IF @bitOrderType = 1 --降序
BEGIN
SET @strOrderType = ' ORDER BY '+@ascColumn+' DESC'
SET @strTemp = '<(SELECT min'
END
ELSE --升序
BEGIN
SET @strOrderType = ' ORDER BY '+@ascColumn+' ASC'
SET @strTemp = '>(SELECT max'
END
IF @currentPage = 1 --第一页
BEGIN
IF @condition != ''
SET @strSql = 'SELECT TOP '+STR(@pageSize)+' '+@columns+' FROM '+@tableName+
' WHERE '+@condition+' '+@strOrderType
ELSE
SET @strSql = 'SELECT TOP '+STR(@pageSize)+' '+@columns+' FROM '+@tableName+@strOrderType
END
ELSE -- 其他页
BEGIN
IF @condition !=''
SET @strSql = 'SELECT TOP '+STR(@pageSize)+' '+@columns+' FROM '+@tableName+
' WHERE '+@condition+' AND '+@pkColumn+@strTemp+'('+@pkColumn+')'+' FROM (SELECT TOP '+STR((@currentPage-1)*@pageSize)+
' '+@pkColumn+' FROM '+@tableName+' where '+@condition+' '+@strOrderType+') AS TabTemp)'+@strOrderType
ELSE
SET @strSql = 'SELECT TOP '+STR(@pageSize)+' '+@columns+' FROM '+@tableName+
' WHERE '+@pkColumn+@strTemp+'('+@pkColumn+')'+' FROM (SELECT TOP '+STR((@currentPage-1)*@pageSize)+' '+@pkColumn+
' FROM '+@tableName+@strOrderType+') AS TabTemp)'+@strOrderType
END
END
EXEC (@strSql)
END
