[MSSQL] 프로시져로 게시판 페이징을 구현

리스트 페이징 프로시져

//리스트 페이징 프로시져
CREATE     PROCEDURE dbo.P_board_list01
	@key_fild	        VARCHAR(50)		= 'midx' ,
	@qstrsql		VARCHAR(500)	= '',
	@TBname		VARCHAR(50)		= '',
	@orderby		VARCHAR(500)	= '',
	@pagesize		INT,
	@page		INT,
	@pagecount	INT OUTPUT,
	@RecordCount	INT OUTPUT,
	@strErrorNum	int OUTPUT
AS
BEGIN Tran
SET NOCOUNT ON
DECLARE @MaxNo INT, @SQL VARCHAR(1000),@NSQL NVARCHAR(1000)
DECLARE @rowcnt INT
SET @rowcnt = 0
SET @strErrorNum = 0
SET @NSQL = N'SELECT @RecordCount=COUNT('+ @key_fild +') FROM '+ @TBname + '(NOLOCK) WHERE 1=1 ' + CONVERT(VARCHAR(500),@qstrsql)
EXEC sp_executesql @NSQL, N'@RecordCount  int output', @RecordCount output
SET @pagecount = CEILING((@RecordCount-1)/@pagesize)+1
SET @SQL = 'SELECT TOP ' + CONVERT(VARCHAR(10), @pagesize)
SET @SQL = @SQL + '     * '
SET @SQL = @SQL + ' FROM  '+ @TBname + ' WHERE  1=1 '
SET @SQL = @SQL + '  AND '+ @key_fild +' NOT IN '    
SET @SQL = @SQL + '      (SELECT TOP ' + CONVERT(VARCHAR(10), ((@page-1) * @pagesize) )
SET @SQL = @SQL + '            '+ @key_fild +' FROM '+ @TBname + '(NOLOCK)  WHERE 1=1 '
SET @SQL = @SQL +  @qstrsql+ @orderby + ') '
SET @SQL = @SQL +  @qstrsql +  @orderby
EXEC (@SQL)
--print @sql
SET @strErrorNum = @@Error
If @strErrorNum <> 0
   BEGIN
	RollBack Tran
   End
Else
   BEGIN
	Commit Tran
   End
SET NOCOUNT OFF

GO

[사용예]

	If kind=0 Or kind=1 Or kind=2 Then
		Qstrsql = " and isdel = '00' "
	End If
	If acc_st_4 <> "" Then		'엑티베이션 상태값
		Qstrsql = Qstrsql & " and A_STATUS='"& acc_st_4 &"' "
	End If

	key_fild = "EVENT_UID"
	orderby = " ORDER BY " & order_query
	TBname = "webmaster_regivic_new"

	iList_size = 20
	iPage = RequestInt("PAGE" , 1)

	sLurl = "fild="& fild &"&U_check="&U_check&"&search_str="& search_str &"&REVENT_SERVICE_KIND="& REVENT_SERVICE_KIND &"&QEVENT_STATUS="&QEVENT_STATUS&"&order_query="& order_query &"&SortField="& SortField &"&SortMethod="& SortMethod &"&sDate="& sDate &"&eDate="& eDate &"&kind="& kind &"&PAGE="

	'------------------------------------------------------------	
	' 리스트 프로시저
	'------------------------------------------------------------

	Set oRs = server.CreateObject("ADODB.Recordset")
	Set oCmd = Server.CreateObject("ADODB.Command")

	With ocmd
		.activeconnection = DbCon
		.commandtext = "P_board_list01"	 
		.commandtype = adcmdstoredproc

		.parameters("@key_fild")	= key_fild
		.parameters("@qstrsql")		= Qstrsql
		.parameters("@TBname")		= TBname
		.parameters("@orderby")		= orderby
		.parameters("@pagesize")	= iList_size
		.parameters("@page")		= iPage

		ors.cursorlocation			= 3
		ors.open ocmd,,adopenforwardonly,adlockreadonly

		pagecount	= .parameters("@pagecount") '--outupt param
		RecordCount = .parameters("@RecordCount") '--outupt param
		strErrorNum = .parameters("@strErrorNum") '--outupt param
		
	End With
	Set ocmd = Nothing

	'------------------------------------------------------------	
	' 리스트 프로시저
	'------------------------------------------------------------

	If ors.eof Or ors.bof Then

		Response.write "검색된 정보가 없습니다."

	Else
		If page = 1 Then 
			i = RecordCount
		Else
			i = RecordCount - (iList_Size * (iPage - 1) )
		End If
		
		Do While Not ors.eof
		
		i = i - 1
		ors.movenext
		Loop
	End If
	ors.close()
	Set ors = Nothing

	Call PrintPage( RecordCount , iPage , iList_Size , pagecount , selfpage &"?"& sLurl )