1--[0] 기본형 게시판(Basic)용 테이블 설계
  2--[!] Drop Table dbo.Basic
  3Create Table dbo.Basic
  4(
  5    Num Int Identity(1, 1) Not Null Primary Key,         --번호
  6    Name VarChar(25) Not Null,                --이름
  7    Email VarChar(100) Null,                 --이메일    
  8    Title VarChar(150) Not Null,                --제목
  9    PostDate DateTime Default GetDate() Not Null,        --작성일    
 10    PostIP VarChar(15) Not Null,                --작성IP
 11    Content Text Not Null,                    --내용
 12    Password VarChar(20) Not Null,                --비밀번호
 13    ReadCount Int Default 0,                --조회수
 14    Encoding VarChar(10) Not Null,                --인코딩(HTML/Text/Mixed)
 15    Homepage VarChar(100) Null,                --홈페이지
 16    ModifyDate SmallDateTime Null,                --수정일    
 17    ModifyIP VarChar(15) Null                --수정IP
 18)
 19Go
 20
 21--[1] 입력 : Write.aspx
 22Insert Basic
 23Values
 24(
 25    '홍길동',
 26    'h@h.com',
 27    '홍길동입니다.(냉무)',
 28    GetDate(),
 29    '127.0.0.1',
 30    '안녕하세요.',
 31    '1234',
 32    0,
 33    'Text',
 34    'http://www.a.com/',
 35    NULL,    --널
 36    ''    --빈(Empty)
 37)
 38Go
 39
 40--[2] 출력 : List.aspx
 41Select 
 42    Num, Name, Email, 
 43    Title, PostDate, ReadCount
 44From Basic --Join On
 45--Where 
 46--Group By
 47--Having
 48Order By Num Desc
 49Go
 50
 51--[3] 상세 : View.aspx
 52Select *
 53From Basic
 54Where Num = 5
 55Go
 56
 57--[4] 수정 : Modify.aspx
 58Begin Tran
 59    Update Basic
 60    Set
 61        Name = '백두산',
 62        Email = 'b@b.com',
 63        Homepage = 'http://b.com/',
 64        Title = '새로운 제목',
 65        Content    = '내용',
 66        Encoding = 'HTML',
 67        ModifyDate = GetDate(),
 68        ModifyIP = '127.0.0.1'
 69    Where Num = 5
 70--RollBack Tran
 71Commit Tran
 72Go
 73
 74--[5] 삭제 : Delete.aspx
 75Begin Transaction
 76    Delete Basic
 77    Where Num = 5
 78--RollBack Transaction
 79Commit Transaction
 80Go
 81
 82--[6] 검색 : Search.aspx
 83Select *
 84From Basic
 85Where
 86    Name Like '%홍길동%'
 87    Or 
 88    Title Like '홍%'
 89    Or
 90    Content Like '%3'
 91Go
 92
 93--[7] 기본형 게시판(Basic)에 글을 작성하는 저장 프로시저 : WriteBasic
 94Create Proc dbo.WriteBasic
 95    @Name VarChar(25), 
 96    @Email VarChar(100), 
 97    @Title VarChar(150), 
 98    @PostIP VarChar(15), 
 99    @Content Text, 
100    @Password VarChar(20), 
101    @Encoding VarChar(10), 
102    @Homepage VarChar(100)    
103--With Encryption
104As
105    Insert Basic
106    (
107        Name, Email, Title, PostIP, Content, 
108        Password, Encoding, Homepage
109    )
110    Values
111    (
112        @Name, @Email, @Title, @PostIP, @Content, 
113        @Password, @Encoding, @Homepage
114    )
115Go
116
117--[8] 기본형 게시판(Basic)에서 데이터를 읽어오는 저장 프로시저 : ListBasic
118Create Procedure dbo.ListBasic
119As
120    Select * 
121    From Basic 
122    Order By Num Desc
123Go
124
125--[9] 조회수 증가시켜주는 저장 프로시저 : UpdateReadCount
126Create Proc dbo.UpdateReadCountBasic
127    @Num Int
128As
129    Update Basic 
130    Set ReadCount = ReadCount + 1 
131    Where Num = @Num
132Go
133
134--[10] 해당 글을 세부적으로 읽어오는 저장 프로시저 : ViewBasic
135Create Procedure dbo.ViewBasic
136    @Num Int
137As
138    Update Basic 
139    Set ReadCount = ReadCount + 1 
140    Where Num = @Num
141
142    Select *
143    From Basic 
144    Where Num = @Num
145Go
146
147--[11] 해당 글에 대한 비밀번호 읽어오는 저장 프로시저 : ReadPassword
148Create Proc dbo.ReadPasswordBasic
149    @Num Int
150As 
151    Select Password 
152    From Basic 
153    Where Num = @Num
154Go
155
156--[12] 해당 글 지우는 저장 프로시저 : DeleteBasic
157Create Proc dbo.DeleteBasic
158    @Password VarChar(20),
159    @Num Int
160As
161    Declare @cnt Int
162    -- 암호와 번호가 맞으면 1을 반환
163    Select @cnt = Count(*) From Basic 
164    Where Num = @Num And Password = @Password
165
166    If @cnt > 0 
167        Delete Basic Where Num = @Num And Password = @Password
168    Else    
169        Return -1
170Go
171
172--[13] 해당 글을 수정하는 저장 프로시저 : ModifyBasic
173Create Proc dbo.ModifyBasic
174    @Name VarChar(25), @Email VarChar(100), 
175    @Title VarChar(150), @ModifyIP VarChar(15), 
176    @Content Text, 
177    @Encoding VarChar(10), @Homepage VarChar(100),
178    @Password VarChar(20), @Num Int
179As
180    Declare @cnt Int
181    Select @cnt = Count(*) From Basic
182    Where Num = @Num And Password = @Password
183
184    If @cnt > 0  -- 넘겨져 온 번호와 암호가 맞는 데이터가 있다면...
185        Update Basic 
186        Set 
187            Name = @Name, Email = @Email,
188            Title = @Title, ModifyIP = @ModifyIP,
189            ModifyDate = GetDate(), Content = @Content,
190            Encoding = @Encoding, Homepage = @Homepage
191        Where Num = @Num And Password = @Password
192    Else
193        Return -1 -- 암호가 틀리면 -1을 반환하자...
194Go
195
196--[14] 검색 저장 프로시저 : 동적 SQL문
197Create Proc dbo.SearchBasic
198    @SearchField VarChar(25),
199    @SearchQuery VarChar(25)
200As
201    Declare @strSql VarChar(150) -- 변수 선언
202    Set @strSql = '
203    Select * From Basic 
204    Where ' 
205        + @SearchField + ' Like ''%' 
206        + @SearchQuery + '%'' Order By Num Desc'
207    --Print @strSql
208    Exec (@strSql)
209Go
210
211SearchBasic ' 1 = 1; Drop Table Basic --', '메롱~'
212Go
213
214SearchBasic 'Name', '홍길동'
215Go
216
217
218--[12] 검색 저장 프로시저 : 정적 쿼리문
219Alter Proc dbo.SearchBasic
220     @SearchField VarChar(25),
221     @SearchQuery VarChar(25)
222As
223    Set @SearchQuery = '%' + @SearchQuery + '%'
224    SELECT *
225    FROM Basic
226    WHERE
227     (
228         CASE @SearchField 
229             WHEN 'Name' THEN Name
230             WHEN 'Title' THEN Title
231             WHEN 'Content' THEN Content
232         ELSE 
233
234             @SearchQuery
235         END
236     ) 
237     LIKE 
238     @SearchQuery
239    Order By Num Desc
240Go
241
242--테스트URL : http://sample.redplus.net/WebBasic/Basic/List.aspx
243--작성자 : 박용준(RedPlus)