47.MS_SQL 2008 - ToDoList
--
TODO LIST 용테이블생성
Create Table Items
(
ID Int Identity(1, 1) Primary Key, --일련번호
[Description]
VarChar(8000) Not Null, --설명
Opened DateTime Default(GetDate()), --등록일
Closed DateTime Null, --완료일
Priority TinyInt Default(1) --우선순위(1:높음,2:보통,3:낮음)
)
Go
--Drop
Table Items
--6가지예시문: 입력, 출력, 상세, 수정, 삭제, 검색
Insert Into Items([Description], Priority)
Values('먹고', 1 )
Insert Into Items([Description], Priority)
Values('게임하고', 2)
Insert Into Items([Description], Priority)
Values('자야지', 3)
Go
Select [Description], Opened, Closed, Priority
From Items
Go
Select * From Items Where Priority = 1
Go
Update Items Set [Description] = '씻어야징'
Where Priority
= 1
Go
Delete From Items Where [Description] = '자야지'
Go
Select * From Items Where [Description] Like '%게임%'
Go
--저장프로시저
Create Procedure
AddItems
@Description
VarChar(8000),
@Priority
TinyInt
As
Insert Into Items(Description, Priority)
Values(@Description, @Priority)
Go
AddItems '밥먹고나서', 2
AddItems '게임해야지!', 1
Create Proc GetItems
As
Select * From Items Order By ID Asc
Go
GetItems
Create Proc GetItemsByPriority
@Priority
TinyInt
As
Select * From Items
Where Priority = @Priority
Go
GetItemsByPriority 1
Create Proc UpdateItems
@Description
VarChar(8000),
@Priority
TinyInt,
@Opened DateTime,
@Closed DateTime,
@ID Int
As
Update Items
Set [Description] = @Description,
Priority
= @Priority,
Opened
= @Opened,
Closed
= @Closed
Where
ID = @ID
Go
Create Proc DeleteItems
@ID Int
As
Delete From Items Where ID = @ID
Go
DeleteItems 2
Create Proc SearchItems
@SearchText
VarChar(50)
As
Declare @Sql VarChar(500)
Set @Sql =
'Select * From
Items
Where Description Like ''%'
+ @SearchText + '%'''
Exec(@Sql)
Go
SearchItems '게임'
47ToDoList.sql