MSSQL에서 동적SQL을 구현(OUTPUT파라미터 있을때)

sp_executesql 라는 스토어드 프러시져를 사용하면 됩니다.

밑에는 실제 프로젝트에 사용했던 프로시져입니다...^^

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


CREATE      procedure p_GetValue (
 @tagName as nvarchar(20),
 @mode as real output,
 @sp as real output,
 @pv as real output,
 @ivp as real output,
 @alm_a as real output,
 @alm_b as real output,
 @alm_c as real output,
 @alm_dbnd as real output,
 @mvp as real output,
 @out as real output,
 @in as real output,
 @ao as real output
)

as


 declare  @tableName                  nvarchar(30)
 declare  @FieldName_mode        nvarchar(30)
 declare  @FieldName_sp                 nvarchar(30)
 declare  @FieldName_pv                 nvarchar(30)
 declare  @FieldName_ivp                nvarchar(30)
 declare  @FieldName_alm_a         nvarchar(30)
 declare  @FieldName_alm_b         nvarchar(30)
 declare  @FieldName_alm_c        nvarchar(30)
 declare  @FieldName_alm_dbnd         nvarchar(30)
 declare  @FieldName_mvp        nvarchar(30)
 declare  @FieldName_out         nvarchar(30)
 declare  @FieldName_in                 nvarchar(30)
 declare  @FieldName_ao                 nvarchar(30)
 declare  @strSQL                 nvarchar(500)
 declare  @strFieldValue                 nvarchar(500)

begin

 set @strSQL = N'select @tableName = tableName, @FieldName_mode = mode, @FieldName_sp = sp, '
 set @strSQL = @strSQL +  N'@FieldName_pv = pv, @FieldName_ivp = ivp, @FieldName_alm_a = alm_a, '
 set @strSQL = @strSQL +  N'@FieldName_alm_b = alm_b, @FieldName_alm_c = alm_c, @FieldName_alm_dbnd = alm_dbnd, '
 set @strSQL = @strSQL + N'@FieldName_mvp = mvp, @FieldName_out = out, @FieldName_in = [in], @FieldName_ao =ao   '

 set @strSQL = @strSQL + N'from tblTagList where TagList_TagName like ''' + @tagName + ''''

 set  @strFieldValue =  N'@tableName nvarchar(30) output, @FieldName_mode nvarchar(30) output, @FieldName_sp nvarchar(30) output, '
 set  @strFieldValue = @strFieldValue + N'@FieldName_pv  nvarchar(30) output,  @FieldName_ivp nvarchar(30) output, @FieldName_alm_a nvarchar(30) output, '
 set  @strFieldValue = @strFieldValue + N'@FieldName_alm_b nvarchar(30) output,   @FieldName_alm_c nvarchar(30) output, @FieldName_alm_dbnd nvarchar(30) output, '
 set  @strFieldValue = @strFieldValue + N'@FieldName_mvp nvarchar(30) output,@FieldName_out nvarchar(30) output, @FieldName_in nvarchar(30) output,  @FieldName_ao nvarchar(30) output '

 
 exec sp_executesql @strSQL, @strFieldValue,  
               @tableName output, @FieldName_mode output, @FieldName_sp output, @FieldName_pv output, @FieldName_ivp output, @FieldName_alm_a output,
               @FieldName_alm_b output, @FieldName_alm_c output, @FieldName_alm_dbnd output, @FieldName_mvp output,  @FieldName_out output,  
               @FieldName_in output,  @FieldName_ao output


 if @tableName is null  return 0

  set @strSQL =  N'select '

 if @FieldName_mode IS Null
     set @strSQL = @strSQL + N' @mode=null'
 else
     set @strSQL = @strSQL + N' @mode= ' + @FieldName_mode

 if @FieldName_sp IS Null
     set @strSQL = @strSQL + N', @sp=null'
 else
     set @strSQL =  @strSQL + N', @sp=' + @FieldName_sp

 if @FieldName_pv IS Null
    set @strSQL =  @strSQL + N', @pv=null'
 else
    set @strSQL =  @strSQL + N', @pv=' + @FieldName_pv

 if @FieldName_ivp IS Null
    set @strSQL = @strSQL +  N', @ivp=null'
 else
    set @strSQL =  @strSQL + N', @ivp=' + @FieldName_ivp

 if @FieldName_alm_a IS  Null
   set @strSQL =  @strSQL + N', @alm_a=null'
 else
    set @strSQL =  @strSQL + N', @alm_a=' + @FieldName_alm_a

 if @FieldName_alm_b IS  Null
    set @strSQL =  @strSQL + N', @alm_b=null'
 else
    set @strSQL =  @strSQL + N', @alm_b=' + @FieldName_alm_b
 
 if @FieldName_alm_c IS  Null
    set @strSQL =  @strSQL + N', @alm_c=null'
 else
    set @strSQL =  @strSQL + N', @alm_c=' + @FieldName_alm_c

 if @FieldName_alm_dbnd  IS  Null
    set @strSQL =  @strSQL + N', @alm_dbnd=null'
 else
    set @strSQL =  @strSQL + N', @alm_dbnd=' +@FieldName_alm_dbnd

 if @FieldName_mvp IS  Null
    set @strSQL =  @strSQL + N', @mvp=null'
 else
    set @strSQL =  @strSQL + N', @mvp=' + @FieldName_mvp

 if @FieldName_out IS  Null
    set @strSQL =  @strSQL + N', @out =null'
 else
    set @strSQL =  @strSQL + N', @out =' + @FieldName_out

 if @FieldName_in IS  Null
     set @strSQL =  @strSQL + N', @in =null'
 else
     set @strSQL =  @strSQL + N', @in =' + @FieldName_in

 if @FieldName_ao IS  Null
     set @strSQL =  @strSQL + N', @ao =null'
 else
     set @strSQL =  @strSQL + N', @ao =' + @FieldName_ao

   set @strSQL = @strSQL + N' from ' + @tableName

 print @strSQL


 exec sp_executesql @strSQL,
              N'@mode real output, @sp real output, @pv real output, @ivp real output, @alm_a real output,  @alm_b real output,  @alm_c real output, @alm_dbnd real output,  @mvp real output,  @out real output,  @in real output,  @ao real output',
              @mode output, @sp output, @pv output, @ivp output, @alm_a output,  @alm_b output,  @alm_c output, @alm_dbnd output,  @mvp output,  @out output,  @in output,  @ao output
end