저장 프로시저에서 데이터 반환

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

프로시저에서 호출 프로그램으로 데이터를 반환하는 방법에는 결과 집합, 출력 매개 변수 및 반환 코드의 세 가지가 있습니다. 이 문서에서는 세 가지 방법에 대한 자세한 정보를 제공합니다.

결과 집합을 사용하여 데이터 반환

저장 프로시저의 본문에 SELECT 문을 포함하는 경우(SELECT ... INTO 또는 INSERT ... SELECT는 포함하지 않음) SELECT 문에 지정된 행이 클라이언트에 직접 전송됩니다. 큰 결과 집합의 경우 저장 프로시저 실행은 결과 집합을 클라이언트에 완전히 전송하기 전까지 다음 문을 계속하지 않습니다. 작은 결과 집합의 경우 결과가 스풀링되어 클라이언트로 반환되고 실행이 계속됩니다. 저장 프로시저를 실행하는 동안 여러 SELECT 문을 실행하는 경우 여러 개의 결과 집합이 클라이언트에 전송됩니다. 이 동작은 중첩된 Transct-SQL 일괄 처리, 중첩된 저장 프로시저 및 최상위 Transact-SQL 일괄 처리에도 적용됩니다.

결과 집합을 사용하여 데이터를 반환하는 예제

다음 예에서는 AdventureWorks2022샘플 데이터베이스를 사용합니다. 이 예제는 vEmployee 보기에도 나타나는 모든 SalesPerson 행에 대해 LastName 및 SalesYTD 값을 반환하는 저장 프로시저를 보여 줍니다.

USE AdventureWorks2022;  
GO

IF OBJECT_ID('Sales.uspGetEmployeeSalesYTD', 'P') IS NOT NULL  
   DROP PROCEDURE Sales.uspGetEmployeeSalesYTD;  
GO  

CREATE PROCEDURE Sales.uspGetEmployeeSalesYTD  
AS    
   SET NOCOUNT ON;

   SELECT LastName, SalesYTD  
   FROM Sales.SalesPerson AS sp  
   JOIN HumanResources.vEmployee AS e ON e.BusinessEntityID = sp.BusinessEntityID;
   
   RETURN;
GO 

출력 매개 변수를 사용하여 데이터 반환

프로시저 정의에서 매개 변수에 출력 키워드를 지정하면 해당 프로시저는 종료될 때 매개 변수의 현재 값을 호출 프로그램에 반환할 수 있습니다. 호출 프로그램에서 사용할 수 있는 변수에 매개 변수 값을 저장하려면 호출 프로그램이 프로시저를 실행할 때 output 키워드를 사용해야 합니다. 출력 매개 변수로 사용될 수 있는 데이터 형식에 대한 자세한 내용은 CREATE PROCEDURE(Transact-SQL)를 참조하세요.

출력 매개 변수의 예

다음 예에서는 입력 및 출력 매개 변수가 있는 프로시저를 보여 줍니다. @SalesPerson 매개 변수는 호출 프로그램에서 지정한 입력 값을 받습니다. SELECT 문은 입력 매개 변수에 전달된 값을 사용하여 올바른 SalesYTD 값을 가져옵니다. 또한 SELECT 문은 @SalesYTD 출력 매개 변수에 값을 할당하여 해당 프로시저가 종료될 때 호출 프로그램으로 값을 반환합니다.

USE AdventureWorks2022;  
GO  
IF OBJECT_ID('Sales.uspGetEmployeeSalesYTD', 'P') IS NOT NULL  
    DROP PROCEDURE Sales.uspGetEmployeeSalesYTD;  
GO  
CREATE PROCEDURE Sales.uspGetEmployeeSalesYTD  
    @SalesPerson nvarchar(50),  
    @SalesYTD money OUTPUT  
AS    
  
    SET NOCOUNT ON;

    SELECT @SalesYTD = SalesYTD  
    FROM Sales.SalesPerson AS sp  
    JOIN HumanResources.vEmployee AS e ON e.BusinessEntityID = sp.BusinessEntityID  
    WHERE LastName = @SalesPerson;

    RETURN;
GO 

다음 예제에서는 첫 번째 예제에서 만든 프로시저를 호출하고 호출된 프로시저에서 반환된 출력 매개 변수 @SalesYTD를 @SalesYTDBySalesPerson 변수에 저장합니다.

예:

• 프로시저의 출력 값을 받을 @SalesYTDBySalesPerson 변수를 선언합니다.
• 입력 매개 변수의 성을 지정하는 Sales.uspGetEmployeeSalesYTD 프로시저를 실행합니다. 출력 값을 @SalesYTDBySalesPerson 변수에 저장합니다.
• PRINT를 호출하여 @SalesYTDBySalesPerson에 저장된 값을 표시합니다.

DECLARE @SalesYTDBySalesPerson money;

EXECUTE Sales.uspGetEmployeeSalesYTD  
    N'Blythe', @SalesYTD = @SalesYTDBySalesPerson OUTPUT;  

PRINT 'Year-to-date sales for this employee is ' +   
    CONVERT(varchar(10),@SalesYTDBySalesPerson);  
GO

프로시저를 실행할 때 출력 매개 변수에 입력 값을 지정할 수도 있습니다. 이렇게 하면 프로시저가 호출 프로그램으로부터 값을 받아 변경하거나 연산을 수행한 다음 호출 프로그램에 새 값을 반환할 수 있습니다. 이전 예제에서는 프로그램에서 Sales.uspGetEmployeeSalesYTD 프로시저를 호출하기 전에 @SalesYTDBySalesPerson 변수에 값을 할당할 수 있습니다. execute 문은 @SalesYTDBySalesPerson 변수 값을 @SalesYTD 출력 매개 변수로 전달할 수 있습니다. 그런 다음 프로시저 본문에서 새 값을 생성하는 계산에 값을 사용할 수 있습니다. 새로운 값은 출력 매개 변수를 통해 프로시저 밖으로 다시 전달되어 프로시저가 종료될 때 @SalesYTDBySalesPerson 변수의 값으로 업데이트됩니다. 이를 "참조별 전달 기능"이라고도 합니다.

프로시저를 호출할 때 매개 변수에 출력을 지정하고 그 매개 변수가 프로시저 정의에서 출력을 사용하여 정의되지 않은 경우 오류 메시지가 나타납니다. 그러나 출력 매개 변수가 있는 프로시저를 실행할 수는 있지만 프로시저를 실행할 때는 출력을 지정할 수 없습니다. 오류가 반환되지는 않지만 호출 프로그램에서 출력 값을 사용할 수 없습니다.

출력 매개 변수에 커서 데이터 형식 사용

Transact-SQL 프로시저는 출력 매개 변수에만 커서 데이터 형식을 사용할 수 있습니다. 매개 변수에 커서 데이터 형식을 지정한 경우에는 프로시저 정의에서 해당 매개 변수에 대해 varying 및 output 키워드 모두 지정되어야 합니다. 매개 변수는 출력으로만 지정될 수 있지만 매개 변수 선언 시 varying 키워드가 지정된 경우에는 데이터 형식은 커서여야 하고 output 키워드도 지정되어야 합니다.

커서 출력 매개 변수 규칙

다음 규칙은 프로시저가 실행될 때 커서 출력 매개 변수와 관련이 있습니다.

• 정방향 전용 커서의 경우 커서의 결과 집합에 반환되는 행은 프로시저 실행이 끝날 때 커서 위치와 그 너머에 있는 행뿐입니다. 예를 들면 다음과 같습니다.

  • 스크롤 불가능 커서는 프로시저에서 100개 행의 RS라는 결과 집합에 대한 프로시저에서 열립니다.

  • 프로시저는 RS 결과 집합의 처음 5개 행을 가져옵니다.

  • 프로시저가 호출자에게 반환됩니다.

  • 호출자에게 반환된 RS 결과 집합은 6에서 100 사이인 RS의 행으로 구성되며 호출자의 커서는 RS의 첫 번째 행 앞에 놓입니다.

• 정방향 전용 커서의 경우, 프로시저의 실행이 끝났을 때 커서가 첫 번째 행보다 앞에 있으면 호출한 일괄 처리, 프로시저, 트리거에 전체 결과 집합이 반환됩니다. 반환되면 커서 위치는 첫 번째 행 앞에 설정됩니다.

• 정방향 전용 커서의 경우, 프로시저가 종료될 때 커서가 마지막 행의 끝 너머에 위치하면 호출하는 배치, 프로시저 또는 트리거에 빈 결과 집합이 반환됩니다.

  참고

  빈 결과 집합은 null 값과 동일하지 않습니다.

• 스크롤 가능 커서의 경우 프로시저가 종료되면 결과 집합의 모든 행이 호출 배치, 프로시저 또는 트리거로 반환됩니다. 반환되면 커서 위치는 프로시저에서 실행된 마지막 인출의 위치에 남아 있습니다.

• 모든 유형의 커서에 대해 커서가 닫혀 있으면 null 값이 호출 배치, 프로시저 또는 트리거로 다시 전달됩니다. 커서가 매개변수에 할당되었지만 해당 커서가 전혀 열리지 않은 경우에도 마찬가지입니다.

  참고

  커서의 닫힌 상태는 반환 시에만 문제가 됩니다. 예를 들어 프로시저를 통해 커서 부분을 닫고, 프로시저의 뒷부분에서 다시 열고, 해당 커서의 결과 집합을 호출 배치, 프로시저 또는 트리거로 반환하는 것이 유효합니다.

커서 출력 매개 변수의 예

다음 예제에서는 @CurrencyCursor 커서 데이터 형식을 사용하여 출력 매개 변수를 지정하는 프로시저를 만듭니다. 그런 다음, 프로시저가 일괄적으로 호출됩니다.

먼저 커서를 선언하여 Currency 테이블에서 여는 프로시저를 만듭니다.

USE AdventureWorks2022;  
GO

IF OBJECT_ID ( 'dbo.uspCurrencyCursor', 'P' ) IS NOT NULL  
    DROP PROCEDURE dbo.uspCurrencyCursor;  
GO

CREATE PROCEDURE dbo.uspCurrencyCursor   
    @CurrencyCursor CURSOR VARYING OUTPUT  
AS  
    SET NOCOUNT ON;

    SET @CurrencyCursor = CURSOR FORWARD_ONLY STATIC FOR  
      SELECT CurrencyCode, Name  
      FROM Sales.Currency;  

    OPEN @CurrencyCursor;  
GO  

다음으로 로컬 커서 변수를 선언하는 배치를 실행하고 지역 변수에 커서를 할당하는 프로시저를 실행한 다음, 커서에서 행을 인출합니다.

USE AdventureWorks2022;  
GO

DECLARE @MyCursor CURSOR;  

EXEC dbo.uspCurrencyCursor @CurrencyCursor = @MyCursor OUTPUT;  

WHILE (@@FETCH_STATUS = 0)  
    BEGIN;  
         FETCH NEXT FROM @MyCursor;  
    END;  

CLOSE @MyCursor;  
DEALLOCATE @MyCursor;  
GO    

반환 코드를 사용하여 데이터 반환

프로시저는 반환 코드라고 하는 정수 값을 반환하여 프로시저의 실행 상태를 나타낼 수 있습니다. RETURN 문을 사용하여 프로시저의 반환 코드를 지정할 수 있습니다. 출력 매개 변수에서와 같이 프로시저가 실행될 때 호출 프로그램에서 사용할 수 있도록 반환 코드 값을 변수에 저장해야 합니다. 예를 들어 int 데이터 형식의 @result 할당 변수는 다음과 같은 my_proc 프로시저의 반환 코드를 저장하는 데 사용됩니다.

DECLARE @result int;

EXECUTE @result = my_proc;
GO

반환 코드는 가능한 각 오류 상황에 대한 반환 코드 값을 설정하기 위해 프로시저 내의 흐름 제어 블록에서 일반적으로 사용됩니다. Transact-SQL 문 다음에 @@ERROR 함수를 사용하면 문이 실행될 때 오류가 발생했는지 여부를 알 수 있습니다. Transact-SQL 반환 코드에서 TRY/CATCH/THROW 오류 처리를 도입하기 전에 저장 프로시저의 성공 또는 실패를 확인하는 데 필요한 경우가 있었습니다. 저장 프로시저는 항상 오류(필요한 경우 THROW/RAISERROR를 사용하여 생성됨)와 함께 오류를 표시하고 오류를 표시하기 위해 반환 코드를 사용하지 않습니다. 또한 반환 코드를 사용하여 애플리케이션 데이터를 반환하지 않아야 합니다.

반환 코드의 예

다음 예는 다양한 오류에 대한 특수 반환 코드 값을 설정하는 오류 처리 기능이 있는 usp_GetSalesYTD 프로시저를 보여줍니다. 다음 표에는 프로시저에서 발생할 수 있는 각 오류에 할당된 정수 값과 각 값에 해당하는 의미가 나와 있습니다.

이 예제는 다음과 같은 Sales.usp_GetSalesYTD라는 프로시저를 만듭니다.

• @SalesPerson 매개 변수를 선언하고 기본값을 NULL로 설정합니다. 이 매개 변수는 영업 사원의 성을 사용합니다.
• @SalesPerson 매개 변수의 유효성을 검사합니다.
  • @SalesPerson이 NULL인 경우 프로시저는 메시지를 출력하고 반환 코드 1을 반환합니다.
  • 그렇지 않고 @SalesPerson 매개 변수가 NULL이 아닌 경우 프로시저는 성이 @SalesPerson 값과 같은 HumanResources.vEmployee 테이블의 행 수를 확인합니다. 개수가 0이면 프로시저는 반환 코드 2를 반환합니다.
• 지정된 성을 가진 영업 사원의 연간 누계 판매량을 쿼리하여 @SalesYTD 출력 매개 변수에 할당합니다.
• @@ERROR(Transact-SQL)를 테스트하여 SQL Server 오류를 확인합니다.
  • @@ERROR가 0이 아니면 프로시저는 반환 코드 3을 반환합니다.
  • @@ERROR가 0이면 프로시저는 @SalesYTD 매개 변수 값이 NULL인지 여부를 확인합니다. 연간 누계 판매를 찾을 수 없는 경우 프로시저는 반환 코드 4를 반환합니다.
  • 위의 조건 중 true인 조건이 없으면 프로시저는 반환 코드 0을 반환합니다.
• 도달하면 저장 프로시저의 마지막 문은 입력 값을 지정하지 않고 저장 프로시저를 재귀적으로 호출합니다.

이 예제의 끝부분에서는 입력 매개 변수의 성을 지정하고 @SalesYTD 변수에 출력 값을 저장하는 동안 Sales.usp_GetSalesYTD 프로시저를 실행하는 코드를 제공합니다.

USE AdventureWorks2022;  
GO
  
CREATE PROCEDURE Sales.usp_GetSalesYTD 
    @SalesPerson NVARCHAR(50) = NULL, 
    @SalesYTD MONEY=NULL OUTPUT
AS
    IF @SalesPerson IS NULL 
    BEGIN
        PRINT 'ERROR: You must specify a last name for the sales person.'
        RETURN (1)
    END
    ELSE 
    BEGIN
        IF(SELECT COUNT(*)FROM HumanResources.vEmployee WHERE LastName=@SalesPerson)=0
            RETURN (2)
    END

    SELECT @SalesYTD=SalesYTD
    FROM Sales.SalesPerson AS sp
         JOIN HumanResources.vEmployee AS e ON e.BusinessEntityID=sp.BusinessEntityID
    WHERE LastName=@SalesPerson;

    IF @@ERROR<>0 
    BEGIN
        RETURN (3)
    END 
    ELSE 
    BEGIN
        IF @SalesYTD IS NULL 
            RETURN (4)
        ELSE 
            RETURN (0)
    END

    EXEC Sales.usp_GetSalesYTD;
GO


DECLARE @SalesYTDForSalesPerson money, @ret_code int;  

EXECUTE Sales.usp_GetSalesYTD  N'Blythe', @SalesYTD = @SalesYTDForSalesPerson OUTPUT;  

PRINT N'Year-to-date sales for this employee is ' +  
    CONVERT(varchar(10), @SalesYTDForSalesPerson);  
GO

다음 예제에서는 usp_GetSalesYTD 프로시저에서 반환되는 반환 코드를 처리하는 프로그램을 만듭니다.

예:

• @SalesYTDForSalesPerson 및 @ret_code 변수를 선언하여 프로시저의 출력 값 및 반환 코드를 받습니다.
• @SalesPerson에 대해 지정된 입력 값을 사용하여 Sales.usp_GetSalesYTD 프로시저를 실행하고 출력 값과 반환 코드를 변수에 저장합니다.
• @ret_code에서 반환 코드를 확인하고 PRINT(Transact-SQL)를 호출하여 적절한 메시지를 표시합니다.

DECLARE @SalesYTDForSalesPerson money, @ret_code int;  
  
EXECUTE @ret_code = Sales.usp_GetSalesYTD  
    N'Blythe', @SalesYTD = @SalesYTDForSalesPerson OUTPUT;  

IF @ret_code = 0  
    BEGIN  
        PRINT 'Procedure executed successfully';
        PRINT 'Year-to-date sales for this employee is ' + CONVERT(varchar(10),@SalesYTDForSalesPerson);
    END  
ELSE IF @ret_code = 1  
   PRINT 'ERROR: You must specify a last name for the sales person.';
ELSE IF @ret_code = 2   
   PRINT 'ERROR: You must enter a valid last name for the sales person.';
ELSE IF @ret_code = 3  
   PRINT 'ERROR: An error occurred getting sales value.';
ELSE IF @ret_code = 4  
   PRINT 'ERROR: No sales recorded for this employee.';
GO

관련 콘텐츠

저장 프로시저 및 관련 개념에 대한 자세한 내용은 다음 문서를 참조하세요.

• CREATE PROCEDURE(Transact-SQL)
• PRINT(Transact-SQL)
• SET @local_variable (Transact-SQL)
• RETURN (Transact-SQL)
• @@ERROR(Transact-SQL)