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

적용 대상:SQL 서버Azure SQL DatabaseAzure SQL Managed InstanceAzure Synapse Analytics분석 플랫폼 시스템(PDW)Microsoft Fabric의 SQL 데이터베이스

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

프로시저에는 최대 2,100개의 매개 변수가 있을 수 있습니다. 각각 이름, 데이터 형식 및 방향을 할당했습니다. 필요에 따라 기본값을 매개 변수에 할당할 수도 있습니다.

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

매개 변수에 값 전달

프로시저 호출에서 제공되는 매개 변수 값은 상수 또는 변수여야 합니다. 함수 이름은 매개 변수 값으로 사용할 수 없습니다. 변수는 @@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을 기본값으로 지정합니다. 프로시저가 매개 변수 값 없이 실행되는 경우 프로시저에서 사용자 지정된 메시지를 반환하도록 하는 것이 좋습니다.

다음 예제에서는 하나의 uspGetSalesYTD 입력 매개 변수를 사용하여 @SalesPerson 프로시저를 만듭니다. 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;

매개 변수 방향 지정

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

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

다음 예제에서는 가격이 지정된 금액을 초과하지 않는 제품 목록을 반환하는 Production.usp_GetList 프로시저를 만듭니다. 이 예제에서는 여러 문과 여러 SELECTOUTPUT 매개 변수를 사용하는 방법을 보여 줍니다. 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)을 반환합니다. OUTPUT 매개 변수 @cost 이며 @compareprices 흐름 제어 언어와 함께 메시지 창에서 메시지를 반환하는 데 사용됩니다.

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)
• Parameters
• EXECUTE(Transact-SQL)