Mssql 동적쿼리 프로시져

동적쿼리.sql

아래 프로시져는 동적쿼리 참고용~ ^^
해보니까 생각보다 쉽네~

-- =============================================
-- Author:  <서공석>
-- Create date: <2010.10.22>
-- Description: <scm daily fcst 최종 자료 생성>
-- =============================================
--exec usp_seo '20101021'
ALTER PROCEDURE  [dbo].[usp_seo]
                                @p_dt             VARCHAR(08)   --계획일자
         
AS

Declare  @week              varchar(06),      -- 주차
             @yyyy              varchar(04),      -- 년도
             @maxversion    varchar(02),      -- dily_fcst의 최종 version
             @msg               varchar(100),
             @Sql_Str           varchar(4000),
             @ii                    smallint,            -- loop 순번
             @lv_itemcd        varchar(30),       -- 품목코드
             @lv_version       smallint,            -- dfc_version
             @lv_planweek    varchar(06),       -- 주차
             @lv_plandt         varchar(08),       -- 계획일자
             @lv_dt               varchar(08),       -- 일자     
             @lv_qty             numeric(12,3)     -- 수량
     
     
CREATE  TABLE #temp01
   ( itemcd             varchar(30)    NULL, 
     version            SMALLINT       NULL, 
     planweek           varchar(06)    NULL,  -- 계획주차
     plandt             varchar(08)    NULL,  -- 계획일자
     DAILY_DATE      varchar(08)    NULL,  -- 일자
     qty              numeric(12,3)  NULL  -- 수량
   )     
  
-- 년도  
Set @yyyy = (select left(@p_dt,4))  

-- 계획일자가 포함된 주차
Set @week = (select yweek from ufn_week_year(@yyyy) where @p_dt between sdate and edate) 

-- dyfc최종 자료
Set @maxversion = (select max(DFC_VERSION)
            from scm..dyfc
            where plan_week = @week and
               category = 'FCST' and
               item_code like '' + '%')
                   
 -- SET NOCOUNT ON added to prevent extra result sets from
 -- interfering with SELECT statements.
 SET NOCOUNT ON;
 
 DECLARE cs1 INSENSITIVE CURSOR FOR
   SELECT item_code,dfc_version,plan_week,plan_date,DAILY1_DATE, DAILY1_qty
    FROM scm..dyfc
    WHERE plan_week = @week and category = 'FCST' AND
       item_code like '' + '%'
   ORDER BY item_code, dfc_version

    FOR READ ONLY

 OPEN cs1

 FETCH NEXT FROM cs1 INTO  @lv_itemcd,@lv_version,@lv_planweek,@lv_plandt,@lv_dt,@lv_qty
 WHILE(@@FETCH_STATUS = 0)
  BEGIN

   INSERT #temp01
   SELECT @lv_itemcd,@lv_version,@lv_planweek,@lv_plandt,@lv_dt,@lv_qty

   IF @@ERROR <> 0
    BEGIN
     SELECT @msg = 'Daily FCST 정렬 중 ERROR(1)'
     CLOSE cs1
     DEALLOCATE cs1
     GOTO error_process
    END
   
   FETCH NEXT FROM cs1 INTO @lv_itemcd,@lv_version,@lv_planweek,@lv_plandt,@lv_dt,@lv_qty
   
  END

CLOSE cs1
DEALLOCATE cs1

----=========================================================================================

Set @ii = 1  

WHILE (@ii < 15)
BEGIN

 select @ii = @ii + 1
 
 select  @Sql_str = 'insert #temp01 '
 select  @Sql_Str = @Sql_str + 'SELECT item_code,dfc_version,plan_week,plan_date,DAILY' + convert(varchar(2),@ii) + '_DATE, DAILY' + convert(varchar(2),@ii) + '_QTY
          FROM scm..dyfc where plan_week = ' + @week + ' and dfc_version = ' + @maxversion + ' and category = ' + char(39) + 'FCST' + char(39)
          
 EXEC (@Sql_Str)
 
 IF @@ERROR <> 0
  BEGIN
   SELECT @msg = 'Daily FCST 정렬 중 ERROR(2)'
   CLOSE cs1
   DEALLOCATE cs1
   GOTO error_process
  END
 
 
END

SELECT * FROM #temp01
ORDER BY 1, 2,5
RETURN

error_process:
RAISERROR (@msg, 16, 1)
RETURN(-1)