sqlserver 單表（視圖）通用分頁存盤程序
create procedure dbo.proc_getpage @table_name varchar(500), --表名，多表連接：tablea a left join tableb b on a.id=b.fk_id @select_fields varchar(1000)=' * ', --需要回傳的列 @page_size int=10, --頁尺寸,如果為0則表示回傳所有行，不分頁 @page_index int=1, --頁碼 @sort_field varchar(50)='', --排序欄位 @order_type varchar(10)='desc', --排序型別, asc|desc @str_where varchar(max)='', --查詢條件(注意: 不要加 where) @row_count int output --總記錄數 as begin declare @select_sql varchar(max) --主陳述句 declare @count_sql nvarchar(max) --獲取總記錄數陳述句 declare @order_sql varchar(400) --排序子陳述句 if(@str_where!='') begin set @count_sql=N'select @rowcount=count(*) from ' + @table_name + N' where ' + @str_where end else begin set @count_sql=N'select @rowcount=count(*) 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 row_no,' + @select_fields + ' from ' + @table_name if(@str_where!='') begin set @select_sql+=' where ' + @str_where end set @select_sql+=') as temp where row_no between ' + cast(((@page_index-1)*@page_size+1) as varchar) + ' and ' + cast((@page_index*@page_size) as varchar) end exec sp_executesql @count_sql,N'@rowcount int output',@row_count output exec(@select_sql) end
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/28288.html
標籤：SQL Server
上一篇：sqlserver重繪視圖