CREATE PROCEDURE dbo.spAll_ReturnRows
(
@SQL nVARCHAR(4000),
@Page int,
@RecsPerPage int,
@ID VARCHAR(255),
@Sort VARCHAR(255)
)
AS
DECLARE @Str nVARCHAR(4000)
SET @Str='SELECT TOP '+CAST(@RecsPerPage AS VARCHAR(20))+' * FROM ('+@SQL+') T WHERE T.'+@ID+' NOT IN
(SELECT TOP '+CAST((@RecsPerPage*(@Page-1)) AS VARCHAR(20))+' '+@ID+' FROM ('+@SQL+') T9 ORDER BY '+@Sort+') ORDER BY '+@Sort
PRINT @Str
EXEC sp_ExecuteSql @Str
GO
(
@SQL nVARCHAR(4000),
@Page int,
@RecsPerPage int,
@ID VARCHAR(255),
@Sort VARCHAR(255)
)
AS
DECLARE @Str nVARCHAR(4000)
SET @Str='SELECT TOP '+CAST(@RecsPerPage AS VARCHAR(20))+' * FROM ('+@SQL+') T WHERE T.'+@ID+' NOT IN
(SELECT TOP '+CAST((@RecsPerPage*(@Page-1)) AS VARCHAR(20))+' '+@ID+' FROM ('+@SQL+') T9 ORDER BY '+@Sort+') ORDER BY '+@Sort
PRINT @Str
EXEC sp_ExecuteSql @Str
GO
二、利用表主鍵法
--参数说明:
--@TableName :要查询表格的名称
--@Page :要查询的页数(第几页)
--@RecsPerPage :每页的条数
--@ID :表格的主键
--@Sort :按哪一列排序(给出列名)
Create PROCEDURE spAll_ReturnRows
(
@TableName nVARCHAR(4000),
@Page int,
@RecsPerPage int,
@ID VARCHAR(255),
@Sort VARCHAR(255)
)
AS
DECLARE @Str nVARCHAR(4000)
SET @Str='SELECT TOP '+CAST(@RecsPerPage AS VARCHAR(20))+' * FROM '+@TableName+' T WHERE T.'+@ID+' NOT IN
(SELECT TOP '+CAST((@RecsPerPage*(@Page-1)) AS VARCHAR(20))+' '+@ID+' FROM '+@TableName+' T9 ORDER BY '+@Sort+') ORDER BY '+@Sort
PRINT @Str
EXEC sp_ExecuteSql @Str
GO
--@TableName :要查询表格的名称
--@Page :要查询的页数(第几页)
--@RecsPerPage :每页的条数
--@ID :表格的主键
--@Sort :按哪一列排序(给出列名)
Create PROCEDURE spAll_ReturnRows
(
@TableName nVARCHAR(4000),
@Page int,
@RecsPerPage int,
@ID VARCHAR(255),
@Sort VARCHAR(255)
)
AS
DECLARE @Str nVARCHAR(4000)
SET @Str='SELECT TOP '+CAST(@RecsPerPage AS VARCHAR(20))+' * FROM '+@TableName+' T WHERE T.'+@ID+' NOT IN
(SELECT TOP '+CAST((@RecsPerPage*(@Page-1)) AS VARCHAR(20))+' '+@ID+' FROM '+@TableName+' T9 ORDER BY '+@Sort+') ORDER BY '+@Sort
PRINT @Str
EXEC sp_ExecuteSql @Str
GO
三、臨時表法
CREATE Procedure m_SelectUser
(
@pagesize int,
@pageindex int
)
As
set nocount on
begin
declare @indextable table(id int identity(1,1),nid int)
declare @PageLowerBound int
declare @PageUpperBound int
set @PageLowerBound=(@pageindex-1)*@pagesize
set @PageUpperBound=@PageLowerBound+@pagesize
set rowcount @PageUpperBound
insert into @indextable(nid) select FID from tUserLoginLogos order by FID desc
select * from tUserLoginLogos p,@indextable t where p.FID=t.nid
and t.id>@PageLowerBound and t.id<=@PageUpperBound order by t.id
set nocount off
end
GO
(
@pagesize int,
@pageindex int
)
As
set nocount on
begin
declare @indextable table(id int identity(1,1),nid int)
declare @PageLowerBound int
declare @PageUpperBound int
set @PageLowerBound=(@pageindex-1)*@pagesize
set @PageUpperBound=@PageLowerBound+@pagesize
set rowcount @PageUpperBound
insert into @indextable(nid) select FID from tUserLoginLogos order by FID desc
select * from tUserLoginLogos p,@indextable t where p.FID=t.nid
and t.id>@PageLowerBound and t.id<=@PageUpperBound order by t.id
set nocount off
end
GO