declare
@mainsql nvarchar(500), --主语句
@tempsql nvarchar(500), --临时变量
@ordersql nvarchar(500), --排序语句
@ordertype int, --排序类型(非0代表降序)
@primarykeyfield varchar(30), --主键字段
@pageindex int, --当前页索引
@pagesize int, --页尺寸
@wherecondition nvarchar(500), --查询条件
@tablename nvarchar(50) --表名
--
set @mainsql=N''
set @tempsql=N''
set @ordersql=N''
set @ordertype=1
set @primarykeyfield=N'id'
set @pageindex=1 --1273723
set @pagesize=100
set @wherecondition=N'cdate between ''2008-7-30 00:00:00'' and ''2008-7-30 23:38:13'''
set @tablename=N'mytable'
--
declare @topn int
set @topn=(@pageindex-1)*@pagesize
--
if(@ordertype!=0)
begin
set @tempsql=N'<(select min'
set @ordersql=N' order by [' + @primarykeyfield + N'] desc'
end
else
begin
set @tempsql=N'>(select max'
set @ordersql=N' order by [' + @primarykeyfield + N'] asc'
end
--
if(@pageindex=1)
begin
set @mainsql=N'select top ' + cast(@pagesize as nvarchar(500)) + N' * from ' + @tablename
if(@wherecondition !='')
begin
set @mainsql = @mainsql + N' where ' + @wherecondition
end
set @mainsql = @mainsql + @ordersql
end
else
begin
set @mainsql = @mainsql + N'select top ' + cast(@pagesize as nvarchar(500)) + N' * from ' + @tablename
set @mainsql = @mainsql + N' where [' + @primarykeyfield + N']' + @tempsql + N'([' + @primarykeyfield + N'])'
set @mainsql = @mainsql + N' from (select top ' + cast(@topn as nvarchar(500)) + N' [' +@primarykeyfield + N'] from ' + @tablename
if(@wherecondition !='')
begin
set @mainsql = @mainsql + N' where ' + @wherecondition
end
set @mainsql = @mainsql + @ordersql + N') as tblTmp)'
if(@wherecondition !='')
begin
set @mainsql = @mainsql + N' and ' + @wherecondition
end
set @mainsql = @mainsql + @ordersql
end
--print @mainsql
declare @parameters nvarchar(500)
set @parameters=N'@mainsql nvarchar(500),
@tempsql nvarchar(500),
@ordersql nvarchar(500),
@ordertype int,
@primarykeyfield varchar(30),
@pageindex int,
@pagesize int,
@wherecondition nvarchar(500),
@tablename nvarchar(50)'
exec sp_executesql @mainsql,@parameters,
@mainsql,
@tempsql,
@ordersql,
@ordertype,
@primarykeyfield,
@pageindex,
@pagesize,
@wherecondition,
@tablename
--
--计算总记录数
declare
@totalsql nvarchar(500), --计算总记录数
@wherecondition nvarchar(500), --查询条件
@tablename nvarchar(50), --表名
@recordcount int --总记录数 --output
--
set @wherecondition=N'cdate between ''2008-7-30 00:00:00'' and ''2008-7-30 23:38:13'''
set @tablename=N'mytable'
--
set @totalsql=N'select @recordcount = count(*) from ' + @tablename
if(@wherecondition !='')
begin
set @totalsql = @totalsql + N' where ' + @wherecondition
end
--print @totalsql
declare @parameters nvarchar(500)
set @parameters=N'@totalsql nvarchar(500),
@wherecondition nvarchar(500),
@tablename nvarchar(50),
@recordcount int output'
exec sp_executesql @totalsql,@parameters,
@totalsql,
@wherecondition,
@tablename,
@recordcount output
select @recordcount as 'recordcount'
@mainsql nvarchar(500), --主语句
@tempsql nvarchar(500), --临时变量
@ordersql nvarchar(500), --排序语句
@ordertype int, --排序类型(非0代表降序)
@primarykeyfield varchar(30), --主键字段
@pageindex int, --当前页索引
@pagesize int, --页尺寸
@wherecondition nvarchar(500), --查询条件
@tablename nvarchar(50) --表名
--
set @mainsql=N''
set @tempsql=N''
set @ordersql=N''
set @ordertype=1
set @primarykeyfield=N'id'
set @pageindex=1 --1273723
set @pagesize=100
set @wherecondition=N'cdate between ''2008-7-30 00:00:00'' and ''2008-7-30 23:38:13'''
set @tablename=N'mytable'
--
declare @topn int
set @topn=(@pageindex-1)*@pagesize
--
if(@ordertype!=0)
begin
set @tempsql=N'<(select min'
set @ordersql=N' order by [' + @primarykeyfield + N'] desc'
end
else
begin
set @tempsql=N'>(select max'
set @ordersql=N' order by [' + @primarykeyfield + N'] asc'
end
--
if(@pageindex=1)
begin
set @mainsql=N'select top ' + cast(@pagesize as nvarchar(500)) + N' * from ' + @tablename
if(@wherecondition !='')
begin
set @mainsql = @mainsql + N' where ' + @wherecondition
end
set @mainsql = @mainsql + @ordersql
end
else
begin
set @mainsql = @mainsql + N'select top ' + cast(@pagesize as nvarchar(500)) + N' * from ' + @tablename
set @mainsql = @mainsql + N' where [' + @primarykeyfield + N']' + @tempsql + N'([' + @primarykeyfield + N'])'
set @mainsql = @mainsql + N' from (select top ' + cast(@topn as nvarchar(500)) + N' [' +@primarykeyfield + N'] from ' + @tablename
if(@wherecondition !='')
begin
set @mainsql = @mainsql + N' where ' + @wherecondition
end
set @mainsql = @mainsql + @ordersql + N') as tblTmp)'
if(@wherecondition !='')
begin
set @mainsql = @mainsql + N' and ' + @wherecondition
end
set @mainsql = @mainsql + @ordersql
end
--print @mainsql
declare @parameters nvarchar(500)
set @parameters=N'@mainsql nvarchar(500),
@tempsql nvarchar(500),
@ordersql nvarchar(500),
@ordertype int,
@primarykeyfield varchar(30),
@pageindex int,
@pagesize int,
@wherecondition nvarchar(500),
@tablename nvarchar(50)'
exec sp_executesql @mainsql,@parameters,
@mainsql,
@tempsql,
@ordersql,
@ordertype,
@primarykeyfield,
@pageindex,
@pagesize,
@wherecondition,
@tablename
--
--计算总记录数
declare
@totalsql nvarchar(500), --计算总记录数
@wherecondition nvarchar(500), --查询条件
@tablename nvarchar(50), --表名
@recordcount int --总记录数 --output
--
set @wherecondition=N'cdate between ''2008-7-30 00:00:00'' and ''2008-7-30 23:38:13'''
set @tablename=N'mytable'
--
set @totalsql=N'select @recordcount = count(*) from ' + @tablename
if(@wherecondition !='')
begin
set @totalsql = @totalsql + N' where ' + @wherecondition
end
--print @totalsql
declare @parameters nvarchar(500)
set @parameters=N'@totalsql nvarchar(500),
@wherecondition nvarchar(500),
@tablename nvarchar(50),
@recordcount int output'
exec sp_executesql @totalsql,@parameters,
@totalsql,
@wherecondition,
@tablename,
@recordcount output
select @recordcount as 'recordcount'