09.ASP.NET - 자료실 게시판(2) : DB설계 - 저장프로시저

--저장프로시저

--[1] 기본게시판(Upload)에글을작성하는저장프로시저: WriteUpload

Create Proc WriteUpload

       @Name VarChar(25),

       @Email VarChar(100),

       @Title VarChar(150),

       @PostIP VarChar(15),

       @Content Text,

       @Password VarChar(20),

       @Encoding VarChar(10),

       @Homepage VarChar(100),

       @FileName VarChar(255),

       @FileSize Int

--With Encryption

As

       Insert Into Upload(Name, Email, Title, PostIP, Content,

                          Password, Encoding, Homepage, FileName, FileSize)

       Values(@Name, @Email, @Title, @PostIP, @Content, @Password,

              @Encoding, @Homepage, @FileName, @FileSize)

Go

 

--[2] 기본게시판(Upload)에서데이터읽어오는저장프로시저: ListUpload

Create Proc ListUpload

As

       Select * From Upload Order By Num Desc

Go

 

--[3] 해당글을세부적으로읽어오는저장프로시저: ViewUpload

Create Proc ViewUpload

       @Num Int

As

       Update Upload Set ReadCount = ReadCount + 1 Where Num = @Num

       Select * From Upload Where Num = @Num

Go

 

--[4] 해당글에대한비밀번호읽어오는저장프로시저: ReadPassword

Create Proc ReadPasswordUpload

       @Num Int

As

       Select Password From Upload Where Num = @Num

Go

 

--[5] 해당글을수정하는저장프로시저: ModifyUpload

Create Proc ModifyUpload

       @Name VarChar(25),

       @Email VarChar(100),

       @Title VarCHar(150),

       @ModifyIP VarChar(15),

       @Content Text,

       @Password VarChar(20),

       @Encoding VarChar(10),

       @Homepage VarChar(100),

       @FileName VarChar(255),

       @FileSize Int,

       @Num Int

As

       Declare @cnt Int

       Select @cnt = COUNT(*) From Upload Where Num = @Num And Password = @Password

      

       If @cnt > 0 -- 넘겨져온번호와암호가맞는데이터가있다면...

             Update Upload Set Name = @Name, Email = @Email,

                               Title = @Title, ModifyIP = @ModifyIP,

                               Content = @Content, Password = @Password,

                               Encoding = @Encoding,

                               Homepage = @Homepage, FileName = @FileName,

                               FileSize = @FileSize Where Num = @Num

       Else

             Return -1 --암호가틀리면-1를반환하자.

Go

 

 

--[6] 해당글을지우는저장프로시저: DeleteUpload

Create Proc DeleteUpload

       @Password VarChar(20),

       @Num Int

As

       Declare @cnt Int

       --암호와번호가맞으면1을반환

       Select @cnt = COUNT(*) From Upload Where Num = @Num And Password = @Password

      

       If @cnt > 0

             Delete From Upload Where Num = @Num And Password = @Password

       Else

             Return -1

Go

 

--[7] 검색: SearchUpload

Create Proc SearchUpload

       @SearchField VarChar(25),

       @SearchQuery VarChar(25)

As

       Declare @Sql VarChar(250)

       Set @Sql = '

       Select * From Upload

       Where ' + @SearchField + ' Like ''%' + @SearchQuery +  '%''

       Order By Num Desc

       '

       Exec(@Sql)

Go

 

SearchUpload 'FileName', 'gif'

 

 

Create Proc dbo.IncreaseDownCountUpload

       @FileName VarChar(255)

As

       Update Upload

       Set DownCount = DownCount + 1

       Where FileName = @FileName

Go

 

Select * From Upload

Go

---

Upload.sql