sqlserver 2005 中的分页存储过程

最新推荐文章于 2022-06-28 17:00:47 发布

love_c2004

最新推荐文章于 2022-06-28 17:00:47 发布

阅读量402

点赞数

文章标签： sqlserver 存储 parameters 优化

本文链接：https://blog.csdn.net/love_c2004/article/details/4374279

版权

可以排序的存储过程，适用于sqlserver2005 ,sqlserver2008。。

写的很匆忙。。。还有很多地方可以优化。。。。

Create PROCEDURE [dbo].[pagination]
    -- Add the parameters for the stored procedure here
    @TableName     nvarchar(200),        ----要显示的表或多个表的连接
    @FieldName     nvarchar(500) = '*',    ----要显示的字段列表
    @FieldSort     nvarchar(200) = '',    ----排序字段列表或条件
    @PageSize      INT = 10,        ----每页显示的记录个数
    @PageIndex     INT = 1,        ----要显示那一页的记录
    //@doCount bit = 0, -- 返回记录总数, 非 0 值则返回
    @OrderType bit = 0, -- 设置排序类型, 非 0 值则降序
    @Condition varchar(1500)=''       -- 查询条件(注意: 不要加where)
AS

declare @strSQL varchar(5000) -- 主语句
declare @rowstart varchar(50) -- 主语句
declare @rowend varchar(50) -- 主语句

BEGIN
set @rowstart = str(@pageSize*(@pageIndex-1))
set @rowend = str(@pageSize*@pageIndex)

if @FieldSort !=''
if @OrderType =0
    if @Condition !=''
       set @strSQL='SELECT * FROM (select *,ROW_NUMBER() Over(order by '+@FieldSort+' asc,ID desc) as rowNum from '+@TableName+ ' where ' + @Condition+')'
    else
       set @strSQL='SELECT * FROM (select *,ROW_NUMBER() Over(order by '+@FieldSort+' asc,ID desc) as rowNum from '+@TableName+')'
else
    if @Condition !=''
       set @strSQL='SELECT * FROM (select *,ROW_NUMBER() Over(order by '+@FieldSort+' desc,ID desc) as rowNum from '+@TableName+ ' where '+@Condition+')'
    else
       set @strSQL='SELECT * FROM (select *,ROW_NUMBER() Over(order by '+@FieldSort+' desc,ID desc) as rowNum from '+@TableName+')'
else
if @Condition!=''
     set @strSQL='SELECT * FROM (select *,ROW_NUMBER() Over(order by ID) as rowNum from '+@TableName +' where '+@Condition +')'
else
     set @strSQL='SELECT * FROM (select *,ROW_NUMBER() Over(order by ID) as rowNum from '+@TableName+')'

set @strSQL=@strSQL + ' QueryTable where rowNum between '+@rowstart+' and '+ @rowend
exec (@strSQL)
END