Sql萬能分頁代碼

標籤：查詢條件   int   HERE   erb   name   記錄   sel   tab   lse   

go  ----萬能分頁代碼
create procedure [dbo].[sp_datapager]
@pagesize int,--每一頁的大小
@pageindex int,--頁碼數
@tablename varchar(Max),--表的名稱
@keycolumn varchar(20),---主鍵id
@columns varchar(200),--要查詢出列的名稱
@where varchar(200),---查詢條件
@orderby varchar(100),---排序方式
@recordcount int out--輸出參數，非0則返回要查詢表的總記錄數

as
declare @sql nvarchar(3000)
declare @rcsql nvarchar(1000)
set @rcsql=‘select @rc=count(*) from ‘[email protected]
set @sql=‘select top ‘+convert(varchar(3),@pagesize)+‘ ‘+ @columns+‘ from ‘+
@tablename +‘ where ‘[email protected]+‘ not in(select top ‘+
convert(varchar(10),(@pageindex-1)*@pagesize)+‘ ‘[email protected]+
‘ from ‘[email protected]+‘)‘
if (@where!=‘‘)
begin
set @rcsql=‘select @rc=count(*) from ‘[email protected]+‘ where ‘[email protected]
set @sql=‘select top ‘+convert(varchar(3),@pagesize)+ @columns+‘ from ‘+
@tablename +‘ where ‘[email protected]+‘ not in(select top ‘+
convert(varchar(10),(@pageindex-1)*@pagesize)+‘ ‘[email protected]+
‘ from ‘[email protected]+‘ where ‘[email protected]+‘) and ‘[email protected]
end
if (@orderby!=‘‘)
begin
if (@where!=‘‘)
begin
set @sql=‘select top ‘+convert(varchar(3),@pagesize)+ @columns+‘ from ‘+
@tablename +‘ where ‘[email protected]+‘ not in(select top ‘+
convert(varchar(10),(@pageindex-1)*@pagesize)+‘ ‘[email protected]+
‘ from ‘[email protected]+‘ where ‘[email protected]+‘ order by ‘[email protected]+‘) and ‘+
@where+‘ order by ‘ [email protected]
end
else
begin
set @sql=‘select top ‘+convert(varchar(3),@pagesize)+ @columns+‘ from ‘+
@tablename +‘ where ‘[email protected]+‘ not in(select top ‘+
convert(varchar(10),(@pageindex-1)*@pagesize)+‘ ‘[email protected]+
‘ from ‘[email protected]+‘ order by ‘[email protected]+‘)‘+‘ order by ‘ [email protected]
end

end
declare @param nvarchar(100)
set @param=‘@rc int output‘
exec sp_executesql @sql
exec sp_executesql @rcsql,@param,@[email protected] output

Sql萬能分頁代碼