1. 예제 테이블 만들기

테스트를 하기 위해서 테이블과 데이터를 생성한다.

————————————————————————————–

CREATE TABLE [dbo].[제품테이블](
[품번] [int] IDENTITY(1,1) NOT NULL,
[품명] [nvarchar](50) NULL,
[단가] [int] NULL,
CONSTRAINT [PK_titles] PRIMARY KEY CLUSTERED ([품번] ASC)
)

GO

INSERT INTO 제품테이블(품명, 단가) VALUES
(‘AA’, ‘1000’)
, (‘AB’, ‘2000’)
, (‘AC’, ‘3000’)
, (‘AD’, ‘4000’)
, (‘AE’, ‘5000’)
, (‘AF’, ‘6000’)
, (‘AG’, ‘7000’)
, (‘AH’, ‘8000’)
, (‘BA’, ‘1000’)
, (‘BB’, ‘2000’)
, (‘BC’, ‘3000’)
, (‘BD’, ‘4000’)
, (‘CE’, ‘5000’)
, (‘CF’, ‘6000’)
, (‘CG’, ‘7000’)
, (‘CH’, ‘8000’)

GO

————————————————————————————–

 

2. OUTPUT 파라미터와 RETURN 값이 있는 저장프로시저 만들기

————————————————————————————–

CREATE PROC usp_UpdatePrice
@rate  float = 0.01
,@product nvarchar(50)
,@ucount int OUTPUT               — 출력파라미터로 지정

AS
SET NOCOUNT ON

UPDATE 제품테이블

 SET 단가 = 단가 + (단가 * @rate)
WHERE 품명 LIKE @product

SET @ucount = @@ROWCOUNT

IF @@ERROR <> 0

    RETURN(-1)                           — 출력값으로 지정

​ ELSE
BEGIN

​       SELECT TOP 1 * FROM 제품테이블
       WHERE 품명 LIKE @product
ORDER BY 단가 DESC

       RETURN(0)— 출력값으로 지정

​
END

————————————————————————————–

참고로 RETURN(값) 에서 값에는 정수만 가능하다.

위와 같이 프로시저를 만들고  아래와 같이 프로시저를 호출하여 OUTPUT 파라미터와 RETURN 값을

확인해 보자

————————————————————————————–

DECLARE @cnt int
DECLARE @rtn int

EXEC @rtn = usp_UpdatePrice -0.1, ‘CE’, @cnt OUTPUT

SELECT @cnt AS 업데이트, @rtn AS 오류

————————————————————————————–

위와 같이 구문을 실행하면 아래와 같은 출력 값을 볼 수 있다.

위의 예제에서 처럼 출력되는 것을 확인할 수 있다.

참고로, 테이블 반환 매개변수는 OUTPUT의 매개변수로 사용할 수 없다.

 

3. 프로시저에서 반환하는 쿼리결과 테이블을 테이블변수에 저장하기

 

쿼리 결과로 테이블을 반환하는 프로시저를 먼저 생성한다.

————————————————————————————–

CREATE PROCEDURE TABLERETURN
AS
BEGIN
SELECT * FROM 제품테이블
END

---

 

1) 테이블변수에 저장하기

위 프로시저에서 출력되는 결과를 테이블 변수에 저장하기

————————————————————————————–

DECLARE @TResults TABLE (
PartID int
,PartName nvarchar(20)
,Price int
)

INSERT @TResults EXEC TABLERETURN

SELECT * FROM @TResults

————————————————————————————–

 

2) 임시테이블에 저장하기

위 프로시저에서 출력되는 결과를 테이블 변수에 저장하기

————————————————————————————–

CREATE TABLE #TResults (
PartID int
,PartName nvarchar(20)
,Price int
)

INSERT #TResults EXEC TABLERETURN

SELECT * FROM #TResults

DROP TABLE #TResults

————————————————————————————–

위 코드를 실행하면 아래와 같이 출력된다.