sqlserver分页存储过程

  • Z时代
  • 2024-01-10
  • 分类：综合

database

sqlserver 单表（视图）通用分页存储过程

createprocedure proc_getpage
@table_namevarchar(100),              --表名（视图）
@select_fieldsvarchar(1000)=" * ",    --需要返回的列
@page_sizeint=10,                     --页尺寸,如果为0则表示返回所有行，不分页
@page_indexint=1,                     --页码
@sort_fieldvarchar(50)="",            --排序字段 
@order_typenvarchar(10)="desc",       --排序类型, asc|desc
@str_wherevarchar(max)=""--查询条件(注意: 不要加 where)
as
begin
declare@select_sqlvarchar(max)       --主语句
declare@count_sqlvarchar(max)        --获取总记录数语句
declare@order_sqlvarchar(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) asvarchar) +" and "+cast((@page_index*@page_size) asvarchar)
end
exec(@count_sql)
exec(@select_sql)
end

 

以上是 sqlserver分页存储过程 的全部内容， 来源链接： utcz.com/z/531452.html

回到顶部