17.jQuery - 메모장 DB 설계

DB설계입니다. MS-SQL로 작업했습니다.
기본 메모장 테이블 설계와 저장 프로시저를 설계하였습니다.

---

Memos 테이블 설계

Create Table dbo.Memos (            Num Int Identity(1, 1) Primary Key,            Name VarChar(25) Not Null,            Email VarChar(50) Null,            Title VarChar(150) Not Null,            PostDate DateTime Default(GetDate()),            PostIP VarChar(15) Null ) Go

---

입력 저장 프로시저 : AddMemo

Create Proc dbo.AddMemo            @Name VarChar(25), @Email VarChar(50),            @Title VarChar(150), @PostIP VarChar(15) As            Insert Into Memos            Values(@Name, @Email, @Title, GetDate(), @PostIP) Go

---

출력 저장 프로시저(기본 페이징) : GetMemos

Create Proc dbo.GetMemos As            Select * From Memos Order By Num Desc Go

---

출력 저장 프로시저(고급 페이징) : GetMemos

Alter Proc dbo.GetMemos -- SQL Server 2005 전용            @Page Int -- 몇번째 페이지 보여줄지? 0 : 1페이지, 99면 : 100페이지 As            Select Top 10 * From Memos            Where                      Num Not In                      (                                 Select Top (10 * @Page) Num From Memos                                 Order By Num Desc                      )            Order By Num Desc Go

---

출력 저장 프로시저(고급 페이징) : GetMemos : 동 적 쿼리문

Alter Proc dbo.GetMemos -- SQL Server 2000에서 사용한다면...            @Page Int As            Declare @strSql VarChar(500)            Set @strSql = '                      Select Top 10 * From Memos                      Where                                 Num Not In                                 (                                           Select Top ' + Cast((10 *  + @Page) As VarChar)                                                      + ' Num From Memos Order By Num Desc                                 )                      Order By Num Desc            '            Exec(@strSql) Go

---

현재 Memos 테이블의 레코드 수 반환 : GetTotalMemo

Create Proc dbo.GetTotalMemo As            Select Count(*) As Total From Memos Go   -- 메모를 검색하는 저장 프로시저 Create Proc dbo.FindMemos            @SearchField VarChar(10),            @SearchQuery VarChar(25) As            Set @SearchQuery = '%' + @SearchQuery + '%'            Select Top 100 * From Memos            Where                      (                                 Case @SearchField                                 When 'Name' Then Name                                 When 'Title' Then Title                                 Else @SearchQuery                                 End                      )                              Like                      @SearchQuery            Order By Num Desc Go   FindMemos 'Name', '홍길' Go

---

검색 결과에 대한 레코드 수 반환

Create Proc dbo.GetTotalFindMemo            @SearchField VarChar(10),            @SearchQuery VarChar(25)     As            Set @SearchQuery = '%' + @SearchQuery + '%'            Select Count(*) From Memos            Where                      (                                 Case @SearchField                                 When 'Name' Then Name                                 When 'Title' Then Title                                 Else @SearchQuery                                 End                      )                              Like                      @SearchQuery Go

---

출력 저장 프로시저(고급 페이징) : GetMemos : 동 적 쿼리문

Create Proc dbo.GetFindMemos -- SQL Server 2000에서 사용한다면...            @Page Int,            @SearchField VarChar(10),            @SearchQuery VarChar(25) As            Declare @strSql VarChar(500)            Set @strSql = '                      Select Top 10 * From Memos                      Where                                 ' + @SearchField + ' Like ''%' + @SearchQuery + '%''                                 And                                   Num Not In                                 (                                           Select Top ' + Cast((10 *  + @Page) As VarChar)                                                      + ' Num From Memos Where ' + @SearchField + ' Like ''%' + @SearchQuery + '%'' Order By Num Desc                                 )                      Order By Num Desc            '            Exec(@strSql) Go

---

예시문 : 100건 데이터 입력

Declare @i Int Set @i = 1 While @i <= 100 Begin            Exec AddMemo '홍길동', 'h@h.com', '안녕하세요.', '127.0.0.1'              Set @i = @i + 1 End

Memo.sql