저장 프로시저 만들기(데이터베이스 엔진)

CREATE PROCEDURE Transact-SQL 문을 사용하여 저장 프로시저를 만들 수 있습니다.

저장 프로시저를 만들기 전에 다음을 고려해야 합니다.

• CREATE PROCEDURE 문을 한 일괄 처리에서 다른 SQL 문과 결합할 수 없습니다.
• 프로시저를 만들려면 데이터베이스에서 CREATE PROCEDURE 권한과 프로시저를 만들고 있는 스키마에 대한 ALTER 권한이 있어야 합니다. CLR 저장 프로시저의 경우 <method_specifier>에서 참조하는 어셈블리를 소유하거나 해당 어셈블리에 대한 REFERENCES 권한이 있어야 합니다.
• 저장 프로시저는 스키마 범위 개체이며 저장 프로시저 이름은 식별자 규칙을 따라야 합니다.
• 현재 데이터베이스에서만 저장 프로시저를 만들 수 있습니다.

저장 프로시저를 만들 때는 다음을 지정해야 합니다.

• 호출 프로시저 또는 일괄 처리로의 입력 매개 변수 및 출력 매개 변수
• 다른 프로시저를 호출하는 등 데이터베이스에서 작업을 수행하는 프로그래밍 문
• 성공 또는 실패 및 실패 이유를 나타내기 위해 호출 프로시저 또는 일괄 처리에 반환되는 상태 값
• 잠재적 오류를 파악하고 처리하는 데 필요한 오류 처리 문
  Microsoft SQL Server 2005에는 저장 프로시저에서 지정할 수 있는 ERROR_LINE 및 ERROR_PROCEDURE와 같은 새로운 오류 처리 함수가 있습니다. 자세한 내용은 Transact-SQL에서 TRY...CATCH 사용을 참조하십시오.

저장 프로시저 이름 지정

저장 프로시저를 만들 때 **sp_**를 접두사로 사용하지 않는 것이 좋습니다. SQL Server에서는 sp_ 접두사를 사용하여 시스템 저장 프로시저를 지정합니다. 따라서 선택한 이름이 일부 미래 시스템 프로시저와 충돌할 수 있습니다. 응용 프로그램에서 스키마가 아닌 정규화된 이름 참조를 사용하고 사용자의 프로시저 이름이 시스템 프로시저 이름과 충돌하는 경우에는 이름이 사용자의 프로시저가 아닌 시스템 프로시저에 바인딩되므로 응용 프로그램이 중단됩니다.

시스템 저장 프로시저와 이름이 동일하고 정규화되지 않았거나 dbo 스키마에 있는 사용자 정의 저장 프로시저는 실행되지 않으며 항상 시스템 저장 프로시저가 대신 실행됩니다. 다음 예에서는 이러한 동작을 보여 줍니다.

USE AdventureWorks;
GO
CREATE PROCEDURE dbo.sp_who
AS
    SELECT FirstName, LastName FROM Person.Contact;
GO
EXEC sp_who;
EXEC dbo.sp_who;
GO
DROP PROCEDURE dbo.sp_who;
GO

명시적 스키마 한정자를 사용할 경우에도 성능이 어느 정도 향상됩니다. 데이터베이스 엔진에서 프로시저를 찾기 위해 여러 스키마를 검색하지 않아도 될 경우 조금 더 빠른 속도로 이름을 확인할 수 있습니다. 자세한 내용은 저장 프로시저 실행을 참조하십시오.

임시 저장 프로시저

# 및 ## 접두사를 프로시저 이름에 추가하여 임시 테이블과 유사한 개인 및 전역 임시 저장 프로시저를 만들 수 있습니다. #는 로컬 임시 저장 프로시저를 나타내며 ##는 전역 임시 저장 프로시저를 나타냅니다. SQL Server가 종료되면 이 프로시저가 존재하지 않습니다.

임시 저장 프로시저는 Transact-SQL 문 또는 일괄 처리 실행 계획의 재사용을 지원하지 않는 이전 버전의 SQL Server에 연결할 때 유용합니다. SQL Server 2000 이상에 연결하는 응용 프로그램은 임시 저장 프로시저 대신 sp_executesql 시스템 저장 프로시저를 사용해야 합니다. 로컬 임시 프로시저를 만든 연결만 이 프로시저를 실행할 수 있으며 연결을 닫을 때 프로시저는 자동으로 삭제됩니다.

모든 연결에서 전역 임시 저장 프로시저를 실행할 수 있습니다. 전역 임시 저장 프로시저는 프로시저를 만든 사용자가 사용하는 연결이 닫히고 현재 다른 연결에서 실행 중인 프로시저 버전이 완료될 때까지 존재합니다. 프로시저를 만드는 데 사용한 연결이 닫히면 더 이상 전역 임시 저장 프로시저를 실행할 수 없습니다. 이미 저장 프로시저를 실행하기 시작한 연결만 완료할 수 있습니다.

tempdb 데이터베이스에서 직접 # 또는 ##로 시작하지 않는 저장 프로시저를 만들면 SQL Server가 종료될 때 저장 프로시저가 자동으로 삭제됩니다. SQL Server가 시작될 때마다 tempdb가 다시 만들어지기 때문입니다. tempdb에서 직접 만들어진 프로시저는 연결이 종료된 다음에도 존재합니다.

[!참고] 임시 저장 프로시저를 자주 사용하면 tempdb의 시스템 테이블에 경합을 초래하여 성능이 저하될 수 있습니다. 이런 경우 sp_executesql을 대신 사용하는 것이 좋습니다. sp_executesql은 시스템 테이블에 데이터를 저장하지 않으므로 이러한 문제를 피할 수 있습니다.

CLR 저장 프로시저는 임시 저장 프로시저로 만들 수 없습니다.

예

1. 단순 프로시저와 복합 SELECT 사용

다음 저장 프로시저는 뷰에서 모든 직원(성과 이름 제공됨), 직함 및 부서 이름을 반환합니다. 이 저장 프로시저는 매개 변수를 사용하지 않습니다.

USE AdventureWorks;
GO
IF OBJECT_ID ( 'HumanResources.uspGetAllEmployees', 'P' ) IS NOT NULL 
    DROP PROCEDURE HumanResources.uspGetAllEmployees;
GO
CREATE PROCEDURE HumanResources.uspGetAllEmployees
AS
    SET NOCOUNT ON;
    SELECT LastName, FirstName, JobTitle, Department
    FROM HumanResources.vEmployeeDepartment;
GO

uspGetEmployees 저장 프로시저는 다음과 같은 방법으로 실행할 수 있습니다.

EXECUTE HumanResources.uspGetAllEmployees;
GO
-- Or
EXEC HumanResources.uspGetAllEmployees;
GO
-- Or, if this procedure is the first statement within a batch:
HumanResources.uspGetAllEmployees;

2. 매개 변수가 있는 단순 프로시저 사용

다음 저장 프로시저는 뷰에서 지정된 직원(성과 이름 제공됨), 직함 및 부서 이름을 반환합니다. 이 저장 프로시저는 전달된 매개 변수와 정확히 일치하는 항목만 허용합니다.

USE AdventureWorks;
GO
IF OBJECT_ID ( 'HumanResources.uspGetEmployees', 'P' ) IS NOT NULL 
    DROP PROCEDURE HumanResources.uspGetEmployees;
GO
CREATE PROCEDURE HumanResources.uspGetEmployees 
    @LastName nvarchar(50), 
    @FirstName nvarchar(50) 
AS 
    
    SET NOCOUNT ON;
    SELECT FirstName, LastName, JobTitle, Department
    FROM HumanResources.vEmployeeDepartment
    WHERE FirstName = @FirstName AND LastName = @LastName;
GO

uspGetEmployees 저장 프로시저는 다음과 같은 방법으로 실행할 수 있습니다.

EXECUTE HumanResources.uspGetEmployees N'Ackerman', N'Pilar';
-- Or
EXEC HumanResources.uspGetEmployees @LastName = N'Ackerman', @FirstName = N'Pilar';
GO
-- Or
EXECUTE HumanResources.uspGetEmployees @FirstName = N'Pilar', @LastName = N'Ackerman';
GO
-- Or, if this procedure is the first statement within a batch:
HumanResources.uspGetEmployees N'Ackerman', N'Pilar';

3. 와일드카드 매개 변수가 있는 단순 프로시저 사용

다음 저장 프로시저는 뷰에서 지정된 직원(성과 이름 제공됨), 직함 및 부서 이름을 반환합니다. 이 저장 프로시저 패턴은 전달된 매개 변수와 일치하고 매개 변수가 없으면 미리 설정된 기본값(D로 시작하는 성)을 사용합니다.

USE AdventureWorks;
GO
IF OBJECT_ID ( 'HumanResources.uspGetEmployees2', 'P' ) IS NOT NULL 
    DROP PROCEDURE HumanResources.uspGetEmployees2;
GO
CREATE PROCEDURE HumanResources.uspGetEmployees2 
    @LastName nvarchar(50) = N'D%', 
    @FirstName nvarchar(50) = N'%'
AS 
    SET NOCOUNT ON;
    SELECT FirstName, LastName, JobTitle, Department
    FROM HumanResources.vEmployeeDepartment
    WHERE FirstName LIKE @FirstName AND LastName LIKE @LastName;
GO

uspGetEmployees2 저장 프로시저는 여러 가지 조합으로 실행할 수 있습니다. 다음에 표시된 것은 이 중 일부입니다.

EXECUTE HumanResources.uspGetEmployees2;
-- Or
EXECUTE HumanResources.uspGetEmployees2 N'Wi%';
-- Or
EXECUTE HumanResources.uspGetEmployees2 @FirstName = N'%';
-- Or
EXECUTE HumanResources.uspGetEmployees2 N'[CK]ars[OE]n';
-- Or
EXECUTE HumanResources.uspGetEmployees2 N'Hesse', N'Stefen';
-- Or
EXECUTE HumanResources.uspGetEmployees2 N'H%', N'S%';

4. OUTPUT 매개 변수 사용

다음 예에서는 가격이 지정된 금액을 초과하지 않는 제품의 목록을 반환하는 uspGetList 저장 프로시저를 만듭니다. 이 예에서는 여러 SELECT 문과 여러 OUTPUT 매개 변수의 사용을 보여 줍니다. OUTPUT 매개 변수는 프로시저를 실행하는 동안 외부 프로시저, 일괄 처리 또는 둘 이상의 Transact-SQL 문이 값 집합에 액세스하도록 허용합니다.

USE AdventureWorks;
GO
IF OBJECT_ID ( 'Production.uspGetList', 'P' ) IS NOT NULL 
    DROP PROCEDURE Production.uspGetList;
GO
CREATE PROCEDURE Production.uspGetList @Product varchar(40) 
    , @MaxPrice money 
    , @ComparePrice money OUTPUT
    , @ListPrice money OUT
AS
    SET NOCOUNT ON;
    SELECT p.[Name] AS Product, p.ListPrice AS 'List Price'
    FROM Production.Product AS p
    JOIN Production.ProductSubcategory AS s 
      ON p.ProductSubcategoryID = s.ProductSubcategoryID
    WHERE s.[Name] LIKE @Product AND p.ListPrice < @MaxPrice;
-- Populate the output variable @ListPprice.
SET @ListPrice = (SELECT MAX(p.ListPrice)
        FROM Production.Product AS p
        JOIN  Production.ProductSubcategory AS s 
          ON p.ProductSubcategoryID = s.ProductSubcategoryID
        WHERE s.[Name] LIKE @Product AND p.ListPrice < @MaxPrice);
-- Populate the output variable @compareprice.
SET @ComparePrice = @MaxPrice;
GO

uspGetList를 실행하여 가격이 700달러 미만인 Adventure Works 제품(자전거) 목록을 반환합니다. @Cost와 @ComparePrices OUTPUT 매개 변수는 흐름 제어 언어와 함께 사용되어 메시지 창에 메시지를 반환합니다.

[!참고] OUTPUT 변수는 프로시저를 만들 때와 변수를 사용할 때 모두 정의해야 합니다. 매개 변수 이름과 변수 이름은 일치하지 않아도 되지만 @ListPrice= variable을 사용하지 않는 경우 데이터 형식과 매개 변수 위치가 일치해야 합니다.

DECLARE @ComparePrice money, @Cost money 
EXECUTE Production.uspGetList '%Bikes%', 700, 
    @ComparePrice OUT, 
    @Cost OUTPUT
IF @Cost <= @ComparePrice 
BEGIN
    PRINT 'These products can be purchased for less than 
    $'+RTRIM(CAST(@ComparePrice AS varchar(20)))+'.'
END
ELSE
    PRINT 'The prices for all products in this category exceed 
    $'+ RTRIM(CAST(@ComparePrice AS varchar(20)))+'.'

다음은 결과 집합의 일부입니다.

Product                                            List Price
-------------------------------------------------- ------------------
Road-750 Black, 58                                 539.99
Mountain-500 Silver, 40                            564.99
Mountain-500 Silver, 42                            564.99
...
Road-750 Black, 48                                 539.99
Road-750 Black, 52                                 539.99

(14 row(s) affected)

These items can be purchased for less than $700.00.

참고 항목

작업

방법: 저장 프로시저 만들기(SQL Server Management Studio)

개념

저장 프로시저 및 트리거 내의 롤백 및 커밋
CLR 저장 프로시저 만들기
저장 프로시저 실행(데이터베이스 엔진)
저장 프로시저 수정
저장 프로시저 다시 컴파일
저장 프로시저 보기
저장 프로시저 삭제

관련 자료

CREATE PROCEDURE(Transact-SQL)
EXECUTE(Transact-SQL)
EXECUTE AS 절(Transact-SQL)
REVERT(Transact-SQL)
컨텍스트 전환
매개 변수 지정
저장 프로시저에서 데이터 반환

도움말 및 정보

SQL Server 2005 지원 받기