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