34,576
社区成员
发帖
与我相关
我的任务
分享
CREATE PROCEDURE [dbo].[sp_Page]
@iPage int=1, --当前页码
@iPageSize int=10,--每页条数
@StrTable varchar(200),--查询的表
@StrText varchar(3000),--查询的字段
@StrWhere varchar(2000),--条件
@StrIndex varchar(30),--索引
@StrOrder varchar(300)='',--排序字段
@StrTotals int output --返回总条数
AS
--定义变量
declare @SqlCount nvarchar(2000)
declare @Sql nvarchar(4000)
declare @TempOrder nvarchar(1000)
if @StrOrder<>''
begin
set @TempOrder=' order by '+@StrOrder
end
else
begin
set @TempOrder=''
end
--组合sql语句
if @iPage=1
begin
set @Sql='select top '+str(@iPageSize)+' '+@StrText+' from '+@StrTable+' where 1=1 '+@StrWhere+@TempOrder
end
else
begin
set @Sql='select top '+str(@iPageSize)+' '+@StrText+' from '+@StrTable+' where '+@StrIndex+' not in (select top '+str(@iPageSize*(@iPage-1))+' '+@StrIndex+' from '+@StrTable+' where 1=1 '+@StrWhere+@TempOrder+') '+@StrWhere+@TempOrder
end
set @SqlCount='select @StrTotals=isnull(count(*),10000) from '+@StrTable+' where 1=1 '+@StrWhere
--查询总记录数量
exec sp_executesql @SqlCount,N'@StrTotals int output',@StrTotals output
--执行sql语句返回
--print (@Sql)
exec (@Sql)
GO
exec sp_Page @iPage =2,@iPageSize=40,@StrTable='Home_ChuShou',@StrText='
id,oTitle,UpTime,Case When IsRecom = 1 And IsRecomTime > getdate() And IsRecomTime > UpTime Then IsRecomTime Else UpTime End As AsDate
'
,@StrWhere='And siteid = 920 And IsZj = 0 And ZjId = 0 And IsChk = 1 And isdel = 0',
@StrIndex='id',@StrOrder='AsDate Desc,UpTime Desc,id Desc',@StrTotals=874
exec sp_Page @iPage =2,@iPageSize=40,@StrTable='Home_ChuShou',@StrText=' id,oTitle,UpTime,Case When IsRecom = 1 And IsRecomTime > getdate() And IsRecomTime > UpTime Then IsRecomTime Else UpTime End As AsDate ' ,@StrWhere='And siteid = 920 And IsZj = 0 And ZjId = 0 And IsChk = 1 And isdel = 0',
@StrIndex='id',@StrOrder='Case When IsRecom = 1 And IsRecomTime > getdate() And IsRecomTime > UpTime Then IsRecomTime Else UpTime End Desc,UpTime Desc,id Desc',@StrTotals=874
' where 1=1 '+@StrWhere+@TempOrder+') '
--你这个打印出来看看就知道了
print (@Sql)
--估计就是别名要先放临时表才能调用的问题,否则select外层获取不到