sqlserver 单表(视图)通用分页存储过程 create procedure proc_getpage @table_name varchar(100), --表名(视图) @select_fie
sqlserver 单表(视图)通用分页存储过程
create procedure proc_getpage
@table_name varchar(100), --表名(视图)
@select_fields varchar(1000)=" * ", --需要返回的列
@page_size int=10, --页尺寸,如果为0则表示返回所有行,不分页
@page_index int=1, --页码
@sort_field varchar(50)="", --排序字段
@order_type nvarchar(10)="desc", --排序类型, asc|desc
@str_where varchar(max)="" --查询条件(注意: 不要加 where)
as
begin
declare @select_sql varchar(max) --主语句
declare @count_sql varchar(max) --获取总记录数语句
declare @order_sql varchar(400) --排序子语句
if(@str_where!="")
begin
set @count_sql="select count(*) as total from " + @table_name + " where " + @str_where
end
else
begin
set @count_sql="select count(*) as total from " + @table_name
end
set @order_sql=" order by " + @sort_field + " " + @order_type
if(@page_size=0)
begin
if(@str_where!="")
begin
set @select_sql="select " + @select_fields + " from " + @table_name + " where " + @str_where + " " + @order_sql
end
else
begin
set @select_sql="select " + @select_fields + " from " + @table_name + " " + @order_sql
end
end
else
begin
set @select_sql="select * from (select row_number() over(" + @order_sql + ") as rowNo," + @select_fields + " from " + @table_name
if(@str_where!="")
begin
set @select_sql+=" where " + @str_where
end
set @select_sql+=") as " + @table_name + " where rowNo between " + cast(((@page_index-1)*@page_size+1) as varchar) + " and " + cast((@page_index*@page_size) as varchar)
end
exec(@count_sql)
exec(@select_sql)
end
--结束END--
本文标题: sqlserver分页存储过程
本文链接: https://www.lsjlt.com/news/2451.html(转载时请注明来源链接)
有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341
下载Word文档到电脑,方便收藏和打印~
2024-05-07
2024-05-07
2024-05-07
2024-05-07
2024-05-07
2024-05-07
2024-05-07
2024-05-07
2024-05-07
2024-05-07
回答
回答
回答
回答
回答
回答
回答
回答
回答
回答
0