Declare @PageSize int
set @PageSize=8
Declare @PageIndex int
set @PageIndex=1
--定义输出的参数
Declare @RecoundCount int
Declare @DateEof Bit --output,--返回数据，是否溢出
Declare @PageCount int --output--返回数据：总页数
Declare @TempResult varchar(1000)
Declare @OrderCommand varchar(1000)
Set @TempResult = 'Select * From dbo.Info_ProductInfo where Display=1 And Audting=0'
Declare @GetRCountSumStr nvarchar(1500)
Set @GetRCountSumStr = 'Select @RecoundCount = Count(*) From (' + @TempResult + ') as t'
Exec sp_executesql @GetRCountSumStr,N'@RecoundCount int output',@RecoundCount output
Declare @ResultMod int
Set @ResultMod = @RecoundCount % @PageSize
If @ResultMod = 0
Set @PageCount = @RecoundCount / @PageSize
Else
Set @PageCount = (@RecoundCount / @PageSize) + 1
Set @DateEof = 0
If @PageIndex < 1
Set @PageIndex = 1
if @PageIndex > @PageCount
Set @PageIndex = @PageCount
Declare @GetProductAuditingList varchar(3000)
Declare @indextable table(id int identity(1,1),PId int)
--'Insert Into @indextable(PId)' + @TempResult
Set @GetProductAuditingList =('
Declare @indextable table(id int identity(1,1),SysId int)
Insert Into @indextable(SysId) Select SysId From (' + @TempResult + ') as t
Select P.*,u.UserName,C.CCnName,T.Id From @indextable as T,dbo.Info_UserInfo As u,Info_CompanyInfo as C,dbo.Info_ProductInfo as p Where Id > ' + cast((@PageSize * @PageIndex) - @PageSize as varchar(10)) + ' And Id <= ' + cast((@PageSize * @PageIndex) as varchar(10)) +
'And P.SysId = T.SysId And p.UserId=u.SysId and P.CompanyId=C.SysID Order By T.Id')
Exec(@GetProductAuditingList)
posted @ 2007-03-22 10:25
ddr888 阅读(380)
评论(6) 编辑 收藏 所属分类:
数据库