저장 프로시저에서 매개 변수 지정

적용 대상:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse AnalyticsAnalytics Platform System(PDW)

프로시저 매개 변수를 지정하면 호출 프로그램에서 값을 프로시저 본문에 전달할 수 있습니다. 이러한 값은 프로시저 실행 시 다양한 목적으로 쓰일 수 있습니다. 프로시저 매개 변수는 매개 변수가 OUTPUT 매개 변수로 표시된 경우 호출 프로그램에 값을 반환할 수도 있습니다.

프로시저는 최대 2100개의 매개 변수를 사용할 수 있으며 각각 이름, 데이터 형식, 방향이 할당됩니다. 필요에 따라 기본값을 매개 변수에 할당할 수도 있습니다.

다음 섹션에서는 값을 매개 변수에 전달하는 것과 프로시저 호출 시 각 매개 변수 특성이 어떻게 사용되는지 알려줍니다.

매개 변수에 값 전달

프로시저 호출에서 제공되는 매개 변수 값은 상수 또는 변수여야 합니다. 함수 이름은 매개 변수 값으로 사용할 수 없습니다. 변수는 @@spid와 같은 사용자 정의 변수 또는 시스템 변수일 수 있습니다.

다음 예제에서는 매개 변수 값을 uspGetWhereUsedProductID 프로시저에 전달하는 방법을 보여줍니다. 다음 예에서는 상수와 변수로 매개 변수를 전달하는 방법과 변수를 사용하여 함수 값을 전달하는 방법을 설명합니다.

USE AdventureWorks2022;  
GO  
-- Passing values as constants.  
EXEC dbo.uspGetWhereUsedProductID 819, '20050225';  
GO  
-- Passing values as variables.  
DECLARE @ProductID int, @CheckDate datetime;  
SET @ProductID = 819;  
SET @CheckDate = '20050225';  
EXEC dbo.uspGetWhereUsedProductID @ProductID, @CheckDate;  
GO

다음 예제에서는 함수를 매개 변수 값으로 전달할 수 없으므로 오류를 반환합니다.

-- Try to use a function as a parameter value.  
-- This produces an error message.  
EXEC dbo.uspGetWhereUsedProductID 819, GETDATE();  

대신 다음 예제와 같이 변수를 사용하여 함수 값을 매개 변수에 전달합니다.

-- Passing the function value as a variable.  
DECLARE @CheckDate datetime;  
SET @CheckDate = GETDATE();  
EXEC dbo.uspGetWhereUsedProductID 819, @CheckDate;  
GO  

매개 변수 이름 지정

프로시저를 만들고 매개 변수 이름을 선언할 하는 경우 매개 변수 이름은 단일 @ 문자로 시작해야 하며 프로시저 범위 에서 고유해야 합니다.

매개 변수 이름을 명시적으로 지정하고 프로시저 호출에서 적절한 값을 각 매개 변수에 할당하면 매개 변수를 원하는 순서로 제공할 수 있습니다. 예를 들어 my_proc 프로시저에 @first, @second 및 @third라는 세 개의 매개 변수가 필요한 경우 프로시저에 전달된 값을 매개 변수 이름에 할당할 수 있습니다.

매개 변수 데이터 형식 지정

CREATE PROCEDURE 문에서 매개 변수가 선언될 때에는 데이터 형식이 함께 정의되어야 합니다. 매개 변수의 데이터 형식은 프로시저가 호출되면 매개 변수에 허용되는 값의 형식과 범위를 결정합니다. 예를 들어 매개 변수를 tinyint 데이터 형식으로 정의하면 해당 매개 변수에 전달될 때 0~255 범위의 숫자 값만 허용됩니다. 프로시저가 데이터 형식과 호환되지 않는 값으로 실행되면 오류가 반환됩니다.

매개 변수 기본값 지정

매개 변수가 선언될 때 지정된 기본값이 있으면 해당 매개 변수는 선택적 매개 변수로 간주됩니다. 프로시저 호출에서 선택적 매개 변수에 대한 값은 제공할 필요가 없습니다.

매개 변수의 기본값이 사용되는 경우는 다음과 같습니다.

• 프로시저 호출 시 매개 변수에 값이 지정되어 있지 않은 경우
• DEFAULT 키워드는 프로시저 호출의 값으로 지정됩니다.

매개 변수에 대한 기본값으로 적절한 값을 지정할 수 없으면 NULL을 기본값으로 지정합니다. 프로시저가 매개 변수 값 없이 실행되는 경우 프로시저에서 사용자 지정된 메시지를 반환하도록 하는 것이 좋습니다.

다음 예제에서는 하나의 @SalesPerson 입력 매개 변수를 사용하여 uspGetSalesYTD 프로시저를 만듭니다. NULL은 매개 변수에 대한 기본값으로 할당되며, @SalesPerson 매개 변수 값 없이 프로시저가 실행되는 경우에 대한 사용자 지정 오류 메시지를 반환하는 오류 처리 문에 사용됩니다.

USE AdventureWorks2022;  
GO  
IF OBJECT_ID('Sales.uspGetSalesYTD', 'P') IS NOT NULL  
    DROP PROCEDURE Sales.uspGetSalesYTD;  
GO  
CREATE PROCEDURE Sales.uspGetSalesYTD  
    @SalesPerson nvarchar(50) = NULL  -- NULL default value  
AS   
    SET NOCOUNT ON;   
  
-- Validate the @SalesPerson parameter.  
IF @SalesPerson IS NULL  
BEGIN  
   PRINT 'ERROR: You must specify the last name of the sales person.'  
   RETURN  
END  
-- Get the sales for the specified sales person and   
-- assign it to the output parameter.  
SELECT SalesYTD  
FROM Sales.SalesPerson AS sp  
JOIN HumanResources.vEmployee AS e ON e.BusinessEntityID = sp.BusinessEntityID  
WHERE LastName = @SalesPerson;  
RETURN  
GO  

다음 예제에서는 프로시저를 실행합니다. 첫 번째 문은 입력 값을 지정하지 않고 프로시저를 실행합니다. 이로 인해 프로시저의 오류 처리 문에서 사용자 지정 오류 메시지를 반환합니다. 두 번째 문은 입력 값을 제공하고 예상된 결과 집합을 반환합니다.

-- Run the procedure without specifying an input value.  
EXEC Sales.uspGetSalesYTD;  
GO  
-- Run the procedure with an input value.  
EXEC Sales.uspGetSalesYTD N'Blythe';  
GO  

기본값이 제공된 매개 변수는 생략할 수 있지만 null을 허용하지 않는 매개 변수 목록은 잘릴 수만 있습니다. 예를 들어 프로시저에 5개의 매개 변수가 있는 경우 @parameter = value를 사용하 매개 변수 이름을 지정하지 않고 네 번째와 다섯 번째 매개 변수를 생략할 수 있습니다. 그러나 매개 변수가 @parameter = value 형식으로 제공되지 않는 한 5번째 매개 변수가 포함되는 경우 4번째 매개 변수는 생략할 수 없습니다.

기본값을 사용하여 여러 매개 변수 지정

매개 변수 이름을 지정하면 매개 변수를 생략할 수 있습니다. 기본값이 NULL인 여러 선택적 매개 변수가 있는 다음 저장 프로시저를 고려해 보세요.

USE AdventureWorks2022;
GO
IF OBJECT_ID ( 'Production.uspSearchList', 'P' ) IS NOT NULL   
    DROP PROCEDURE Production.uspSearchList;  
GO  
CREATE PROCEDURE Production.uspSearchList
      @ListPrice money 
    , @ProductCategoryID int       = NULL  -- NULL default value  
    , @ProductSubcategoryID int    = NULL  -- NULL default value  
    , @ProductBusinessEntityID int = NULL  -- NULL default value  
AS  
    SET NOCOUNT ON;  
    SELECT 
        p.Name, p.Class, p.ListPrice, p.ProductID, pc.Name, psc.Name, v.Name
    FROM 
        Production.Product AS p
    INNER JOIN Production.ProductSubCategory AS psc ON p.ProductSubcategoryID = psc.ProductSubcategoryID
    INNER JOIN Production.ProductCategory AS pc ON psc.ProductCategoryID = pc.ProductCategoryID
    INNER JOIN Purchasing.ProductVendor AS pv ON p.ProductID = pv.ProductID
    INNER JOIN Purchasing.Vendor AS v ON pv.BusinessEntityID = v.BusinessEntityID
    WHERE (p.ListPrice < @ListPrice)
    AND   (pc.ProductCategoryID = @ProductCategoryID or @ProductCategoryID IS NULL)
    AND   (psc.ProductSubcategoryID = @ProductSubcategoryID or @ProductSubcategoryID IS NULL)
    AND   (pv.BusinessEntityID = @ProductBusinessEntityID or @ProductBusinessEntityID IS NULL);
GO

각 매개 변수의 이름이 @parameter = value 형식으로 제공되는 한 다음 일련의 예제에서 볼 수 있듯이 기본값을 사용하여 매개 변수를 지정하거나 생략할 수 있습니다.

--Find all Products with a list price less than 150.00 and in the ProductCategoryID = 4
EXEC Production.uspSearchList @ListPrice = 150, @ProductCategoryID = 4;
--Find all Products with a list price less than 150.00 and in the ProductSubCategoryID = 36
EXEC Production.uspSearchList @ListPrice = 150, @ProductSubCategoryID = 36;
--Find all Products with a list price less than 150.00 and from @ProductBusinessEntityID = 1498
EXEC Production.uspSearchList @ListPrice = 150, @ProductBusinessEntityID = 1498;
--Find all Products with a list price less than 150.00 and in the ProductSubCategoryID = 36 and from @ProductBusinessEntityID = 1498
EXEC Production.uspSearchList @ListPrice = 150, @ProductCategoryID = 4, @ProductBusinessEntityID = 1498;

매개 변수 이름이 제공되면 모든 후속 매개 변수를 동일한 방식으로 제공해야 하므로 다음 예제는 유효한 T-SQL 구문이 아닙니다. 오류와 혼동을 방지하려면 항상 매개 변수 이름을 모든 값에 제공하는 것이 좋습니다.

EXEC Production.uspSearchList @ListPrice = 150, 4, 1498;

매개 변수 방향 지정

매개 변수의 방향은 프로시저 본문으로 전달되는 값을 의미하는 입력 또는 프로시저가 호출 프로그램에 값을 반환함을 의미하는 출력입니다. 기본값은 입력 매개 변수입니다.

출력 매개 변수를 지정하려면 CREATE PROCEDURE 문의 매개 변수 정의에서 OUTPUT 키워드를 반드시 지정해야 합니다. 프로시저는 출력 매개 변수의 현재 값을 프로시저가 끝날 때 호출 프로그램에 반환합니다. 호출 프로그램에서 프로시저를 실행할 때 OUTPUT 키워드도 사용해야 호출 프로그램에서 사용할 수 있는 변수에 매개 변수 값을 저장할 수 있습니다.

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

USE AdventureWorks2022;  
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  

usp_GetList를 실행하여 가격이 $700 미만인 Adventure Works 제품 목록(Bikes)을 반환합니다. @cost 및 @compareprices OUTPUT 매개 변수는 흐름 제어 언어와 함께 사용되어 메시지 창에 메시지를 반환합니다.

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.  

다음 단계

• CREATE PROCEDURE(Transact-SQL)
• 매개 변수
• EXECUTE(Transact-SQL)