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

리스트 페이징 프로시져

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43

//리스트 페이징 프로시져 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

[사용예]

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70

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 )