use [hdis] go /****** object: storedprocedure [dbo].[aspnetpager] script date: 12/30/2014 09:00:35 ******/ set ansi_nulls on go set quoted_identifier on go create procedure [dbo].[aspnetpager] (@tablename nvarchar (1000), --表名 @filedname
use [hdis]
go
/****** object: storedprocedure [dbo].[aspnetpager] script date: 12/30/2014 09:00:35 ******/
set ansi_nulls on
go
set quoted_identifier on
go
create procedure [dbo].[aspnetpager]
(@tablename nvarchar (1000), --表名
@filedname nvarchar (4000), --查询的字段
@startindex int, --起始记录数
@endindex int, --结束记录数
@where nvarchar (4000), --条件 (不包含where)
@orderfiled nvarchar (100), --排序字段 （createdate desc）
@pagesize int,
@prmkeyname nvarchar (100),
@pageindex int,
@docount bit)
as
begin
declare @date varchar(50),@sql nvarchar (4000) ,@i int
select @date =convert(nvarchar(50), serverproperty('productversion'))
--if(convert(int, substring(@date,0,3))>8) ------sql2000以上
-- begin
-- if(@docount=1)
-- set @sql = 'select count(*) from ' + @tablename +' where ' + @where
-- else
-- begin
-- set @sql ='
-- with temptbl as (
-- select row_number() over (order by '+ @orderfiled +' )as row, * from '+ @tablename +' where '+ @where +')
-- select '+ @filedname +' from temptbl where row between '+convert(nvarchar(100),@startindex) +' and '+convert(nvarchar(100),@endindex )
-- end
-- exec (@sql)
-- end
--else
begin -------sql2000
if(@docount=1)
set @sql = 'select count(*) from ' + @tablename +' where ' + @where
else
begin
set @i= convert(nvarchar(100),@pagesize)*(convert(nvarchar(100),@pageindex)-1)
set @sql = 'select top '+ convert(nvarcha【本文来自鸿网互联 (http://www.68idc.cn)】r(100),@pagesize) +' *
from ' + @tablename +' where ('+@where +' and'+@prmkeyname+' not in
(select top '+convert(nvarchar(100),@i)+' ' +@prmkeyname +'
from ' + @tablename +' where ' + @where+' order by '+ @orderfiled +')) order by '+ @orderfiled
end
--print(@sql)
exec (@sql)
end
end