SQL Server 강의실

시삽: 레드플러스 님 
게시판 이동:
 제목 : 매개변수사용저장프로시저예제1.sql
글번호: 39
작성자: 레드플러스 (  )
작성일: 2006/01/02 오전 11:39:00
조회수: 5321
--[1] SP연습용 DB 생성
Use Master
GO

CREATE DATABASE StudyStoredProcedure
GO

sp_helpdb StudyStoredProcedure

Use StudyStoredProcedure
GO

--[2] SP연습용 Table 생성
Create Table [dbo].[Products]
(
    [ProductID] int identity (1, 1) PRIMARY KEY NOT NULL ,
    [CategoryID] int NOT NULL ,
    [ModelNumber] VarChar (50) ,
    [ModelName] VarChar (50) ,
    [ProductImage] VarChar (50) ,
    [UnitCost] money NOT NULL ,
    [Description] VarChar (3800)
)
GO

Insert Products
Values(1,'test1','test','test',55,'test')
Insert Products
Values(2,'test2','test','test',155,'test')
Insert Products
Values(3,'test3','test','test',255,'test')

--[3] Products테이블에 있는 모든 필드를 가져오는 프로시져 생성
CREATE PROC ProductList
AS
    SELECT * FROM Products
GO

--프로시져 수행
EXECUTE ProductList
EXEC ProductList
ProductList

--[4] UnitCost가 얼마 이상인 값만 조회하고자 할 때의 프로시져 생성
CREATE PROC ProductsByUnitCost
    @UnitCost int
AS
    SELECT * FROM Products
    WHERE UnitCost > @UnitCost
GO

--프로시져 수행
EXEC ProductsByUnitCost 50
EXEC ProductsByUnitCost 200

--[5] 문자열 데이터를 처리하는 프로시저 생성
CREATE PROC ProductsByModelNumber
    @ModelNumber VarChar(50)
AS
    SELECT * FROM Products
    WHERE ModelNumber = @ModelNumber
Go

--프로시저 수행
EXEC ProductsByModelNumber 'test1'
EXEC ProductsByModelNumber 'test2'

--[6] 다중 매개변수 처리 : Products테이블에 값을 입력하는 저장 프로시저
Create Proc ProductsAdd
(
    @CategoryID int,
    @ModelNumber VarChar(50),
    @ModelName VarChar(50),
    @ProductImage VarChar(50),
    @UnitCost money,
    @Description VarChar(3800)
)
AS
    Insert Into Products
    Values
    (
        @CategoryID,
        @ModelNumber,
        @ModelName,
        @ProductImage,
        @UnitCost,
        @Description
    )
    
    Select @@Identity --방금 전에 입력된 Identity 값을 반환
Go

--프로시저 수행
ProductsAdd 1, 'test4', 'test', 'test', 155, 'test'

--Select * From Products Where ModelNumber = 'test4'

--[7] 문자열 연결 방식으로 매개변수 사용 : 동적 SQL문
--SELECT * FROM Products
--WHERE modelNumber = 'test1'
CREATE PROC SearchList
    @tblName VarChar(20),
    @modelNumber VarChar(50)
AS
    DECLARE @strSql VarChar(200)
    SET @strSql =
        'SELECT * FROM ' + @tblName
        + ' WHERE modelNumber = ''' --작은따옴표 3개
        + @modelNumber + '''' --작은따옴표 4개
    Print @strSql
    EXEC(@strSql)
GO

--프로시저 수행
EXEC SearchList 'Products', 'test1'
EXEC SearchList 'Products', 'test2'

--[8] TOP구문시 주의사항 : int형 사용하지 말 것.
--Select Top 4 * From Products
CREATE PROC productsTopN
    @TopN VarChar(3) --Top n 이라해도 가변길이문자열로 처리할 것.
AS
    DECLARE @strSql VarChar(150)
    SET @strSql =
        'SELECT Top ' + @TopN
        + ' * FROM Products'
    Print @strSql
    EXEC(@strSql)
Go

--프로시저 수행
EXEC productsTopN '2'

--[9] 검색 기능 구현시(Like문 사용) : 동적 SQL문
--Select * From Products
--Where ModelNumber Like '%test%'
CREATE PROC SearchProducts
    @modelNumber VarChar(50)
AS
    DECLARE @strSql VarChar(150)
    SET @strSql =
        'SELECT * FROM Products'
        + ' WHERE ModelNumber
        like ''%' + @modelNumber + '%'''
    Print @strSql
    EXEC (@strSql)
Go

--프로시저 수행
EXEC SearchProducts 'test'
EXEC SearchProducts 'test3'

--[10] 연습용 DB 삭제
--Use Master
--Go

--Drop DataBase StudyStoredProcedure
--GO
 
이전 글   다음 글 삭제 수정 답변 글쓰기 리스트

(댓글을 남기려면 로그인이 필요합니다.)

관련 아티클 리스트
  제       목 파일 작성자 작성일 조회
이전글 17. 트랜잭션 및 트리거 - 레드플러스 2003-12-05 3923
  16. 저장 프로시저 2 - 레드플러스 2003-12-05 3872
현재글 매개변수사용저장프로시저예제1.sql - 레드플러스 2006-01-02 5321
  OUTPUT매개변수와RETURN사용저장프로시저예제2.sql - 레드플러스 2006-01-02 6004
다음글 15. 저장 프로시저 1 - 레드플러스 2003-12-05 3889
 



손님 사용자 Anonymous (손님)
로그인 Home