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

2025-01-02

적용 대상:SQL Server Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Microsoft Fabric의 SQL 데이터베이스

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

이 문서의 코드 샘플은 AdventureWorks2022 또는 AdventureWorksDW2022 샘플 데이터베이스를 사용합니다. 이 데이터베이스는 Microsoft SQL Server 샘플 및 커뮤니티 프로젝트 홈페이지에서 다운로드할 수 있습니다.

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

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

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

이 예제는 LastName 보기에도 나타나는 모든 SalesYTD 행에 대해 SalesPerson 및 vEmployee 값을 반환하는 저장 프로시저를 보여 줍니다.

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
     INNER JOIN HumanResources.vEmployee AS e
         ON e.BusinessEntityID = sp.BusinessEntityID;
RETURN;
GO

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

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

출력 매개 변수의 예

다음 예에서는 입력 및 출력 매개 변수가 있는 프로시저를 보여 줍니다. @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
     INNER 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 AS MONEY;

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

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

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

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

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

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

참고

커서 데이터 형식은 OLE DB, ODBC, ADO 및 DB-Library와 같은 데이터베이스 API를 통해 애플리케이션 변수에 바인딩할 수 없습니다. 애플리케이션이 프로시저를 실행하기 전에 출력 매개 변수를 바인딩해야 하므로 데이터베이스 API에서 커서 출력 매개 변수가 있는 프로시저를 호출할 수 없습니다. 이러한 프로시저는 Transact-SQL 로컬 커서 변수에 커서 출력 변수가 할당된 경우에만 Transact-SQL 일괄 처리, 프로시저 또는 트리거에서 호출할 수 있습니다.

커서 출력 매개 변수 규칙

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

정방향 전용 커서의 경우 커서의 결과 집합에 반환된 행은 프로시저 실행이 끝날 때 커서 위치와 그 너머의 행만 반환합니다. 예를 들어:
- 스크롤할 수 없는 커서가 프로시저에서 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 AS CURSOR;

EXECUTE dbo.uspCurrencyCursor
    @CurrencyCursor = @MyCursor OUTPUT;

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

CLOSE @MyCursor;

DEALLOCATE @MyCursor;
GO

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

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

DECLARE @result AS INT;
EXECUTE @result = my_proc;
GO

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

반환 코드의 예

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

반환 코드 값	의미
`0`	성공한 실행.
`1`	필수 매개 변수 값이 지정되지 않았습니다.
`2`	지정된 매개 변수 값이 잘못되었습니다.
`3`	판매 값을 가져오는 중에 오류가 발생했습니다.
`4`	영업 사원의 `NULL` 판매액이 발견되었습니다.

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

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

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

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
     INNER 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
EXECUTE Sales.usp_GetSalesYTD;
GO

DECLARE @SalesYTDForSalesPerson AS MONEY, @ret_code AS 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 변수를 선언하여 프로시저의 출력 값 및 반환 코드를 받습니다.
Sales.usp_GetSalesYTD에 대해 지정된 입력 값을 사용하여 @SalesPerson 프로시저를 실행하고 출력 값과 반환 코드를 변수에 저장합니다.
@ret_code의 반환 코드를 확인하고 적절한 메시지를 표시하기 위해 에서 PRINT를 호출합니다.

DECLARE @SalesYTDForSalesPerson AS MONEY, @ret_code AS 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