ms-sql 페이징 쿼리

ms-sql 2000 버전용[페이징 쿼리문]

CREATE PROCEDURE dbo.StoredProcedurePaging2000

(

@currentPageNo int,

@pageSize int,

@totalRows int output

)

AS

begin

set nocount on

declare @sql nvarchar(500)

set @sql = 'SELECT TOP '+ STR(@pageSize)

+ '* '

+ 'FROM Member '

+ 'WHERE no NOT IN ( '

+ 'SELECT TOP ' + STR(((@currentPageNo - 1) * @pageSize))

+ ' no '

+ 'FROM Member '

+ 'ORDER BY no DESC)'

+ 'ORDER BY no DESC'

exec sp_executesql @sql

SELECT @totalRows = (SELECT COUNT(*) FROM Member)

endexec sp_executesql @sql

end

ms-sql 2005 버전용[페이징 쿼리문]

CREATE PROCEDURE dbo.StoredProcedurePaging2005

(

@currentPageNo int,

@pageSize int,

@totalRows int OUTPUT

)

AS

BEGIN

SET NOCOUNT ON

SELECT *

FROM ( SELECT ROW_NUMBER() OVER ( ORDER BY no DESC ) AS _ROWNUM, * 

FROM Member

) AS tb

WHERE tb._ROWNUM BETWEEN ((@currentPageNo - 1) * @pageSize + 1)

AND (@currentPageNo * @pageSize)

SELECT @totalRows = (SELECT COUNT(*) FROM Member)

END