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