[MSSQL] 프로시져 내 반복문 사용 쿼리

[MSSQL] 프로시져(PROCEDURE) 내 반복문(LOOP) 사용 쿼리

while를 사용하여 반복문 구현

 

프로시저 생성쿼리 :

CREATE PROCEDURE [dbo].[PROC_TEST]
(
	  @OUT_DBMS_CODE		INT             OUTPUT
	, @OUT_DBMS_MSG			NVARCHAR(MAX)   OUTPUT
)
AS
BEGIN
	/* 변수선언 */
	DECLARE @REC
	  TABLE
	  (
		  ROWNUM	INT
		, TEXT1		NVARCHAR(255) 
	  )

	DECLARE @IDX		INT
	      , @MAX_CNT	INT
	      , @L_TEXT1	NVARCHAR(255)

	/* OUTPUT 변수 초기화 */
	SET @OUT_DBMS_CODE  = 0;    
	SET @OUT_DBMS_MSG   = '';   

	BEGIN TRY
	    /* 임시 테이블 생성*/
		WITH TMP_A AS
		(
		  SELECT '일월' AS TEXT1, 1 AS ORD
		   UNION ALL
		  SELECT 'FEB' AS TEXT1, 2 AS ORD
		   UNION ALL
		  SELECT '3월' AS TEXT1, 3 AS ORD
		   UNION ALL
		  SELECT '사월' AS TEXT1, 4 AS ORD
		   UNION ALL
		  SELECT 'MAY' AS TEXT1, 5 AS ORD
		   UNION ALL
		  SELECT '6월' AS TEXT1, 6 AS ORD
		)

		INSERT @REC
		SELECT ROW_NUMBER()OVER(ORDER BY ORD) AS ROW_NUM
		     , TEXT1
		  FROM TMP_A
	  END TRY
	BEGIN CATCH
		ROLLBACK;
		SET @OUT_DBMS_CODE  = ERROR_NUMBER();
		SET @OUT_DBMS_MSG   = ERROR_MESSAGE();
	  END CATCH
	
	SELECT @IDX = 1
	     , @MAX_CNT = MAX(ROWNUM)
	  FROM @REC
	
     WHILE @IDX <= @MAX_CNT
	 BEGIN
		 SELECT @L_TEXT1 = A.TEXT1
		   FROM @REC A
		  WHERE A.ROWNUM = @IDX;

		  PRINT CONVERT(NVARCHAR(255), @IDX) + ' : ' + @L_TEXT1;

		  SET @IDX = @IDX + 1;
	   END

END

 

실행 소스 : 

DECLARE  @OUT_DBMS_CODE		INT           
       , @OUT_DBMS_MSG		NVARCHAR(MAX)    

EXEC PROC_TEST @OUT_DBMS_CODE OUTPUT, @OUT_DBMS_MSG OUTPUT

 

쿼리실행 화면