ALTER procedure [dbo].[fenye]
@pagesize int, --每頁顯示數量
@pageCurrent int, --當前頁
@tablename varchar(20), --表名
@field varchar(20), --顯示的列名(eg: id,name)
@where varchar(20), --篩選條件 (eg: name not null)
@orderBy varchar(20), --排序的列名（eg: id 或者 id desc）
@count int output --返回總共有多少頁,0 為不要返回 1 位
as
begin
declare @strSql nvarchar(200)
declare @starNum int
declare @endNum int
set @starNum =(@pageCurrent -1)* @pagesize
set @endNum =@pageCurrent * @pagesize
--declare @timediff datetime
--set nocount on --不返回計數（表示受Transact-SQL 語句影響的行數）。
--select @timediff=getdate() --記錄時間
if @count !=0
begin
if @where = ''
set @strSql ='select @count=count(*) from '+@tablename
else
set @strSql ='select @count=count(*) from '+@tablename+' where '+@where
end
exec sp_executesql @strSql,N'@count int output,@tablename varchar(20),@where varchar(20)',@count output,@tablename,@where
if @pageCurrent =1
if @where = ''
set @strSql ='select top '+cast(@pagesize as varchar)+' '+@field+' from '+@tablename+' order by '+@orderBy+''
else
set @strSql ='select top '+cast(@pagesize as varchar)+' '+@field+' from '+@tablename+' where '+@where+' order by '+@orderBy+''
else
if @where !=''
set @strSql='select '+@field+' from (select '+@field+',row_number() over(order by '+@orderBy+') rn from '+@tablename+' where '+@where+')a where rn<='+CONVERT(varchar,@endNum)+' and rn>'+cast(@starNum as varchar)+''
else
set @strSql='select '+@field+' from (select '+@field+',row_number() over(order by '+@orderBy+') rn from '+@tablename+')a where rn<='+CONVERT(varchar,@endNum)+' and rn>'+cast(@starNum as varchar)+''
exec(@strSql)
--select datediff(ms,@timediff,getdate()) as 耗時
--set nocount off --返回計數(默認為OFF)。
end
?
declare @count int
set @count=1
exec fenye 3,3,cj,'*','fenshu is not null','id',@count output
select @count
