newtype
::: newtype의 지식창고 :::
newtype
전체 방문자
오늘
어제
  • 분류 전체보기 (392)
    • Dev (214)
      • C, C++ (43)
      • Go (5)
      • Web (49)
      • DBMS (21)
      • DevOps (8)
      • Java (2)
      • Windows, Win32 (4)
      • Visual Basic (5)
      • C# (2)
      • Mobile (25)
      • SQL CE (7)
      • Google Map (6)
      • Python (2)
      • cygwin (2)
      • 기타 (32)
      • Ruby (1)
    • 명언 (10)
    • 모임 (18)
      • 붕주회 (3)
      • 신흥컴정 (14)
      • 웹20기 (1)
    • 사진 (8)
    • 불펌 (29)
    • 막글 (98)
    • 게임 (6)
    • 여행 (8)

블로그 메뉴

  • 홈
  • 태그
  • 방명록
  • 관리

공지사항

  • whoami
05-11 10:21
hELLO · Designed By 정상우.
newtype

::: newtype의 지식창고 :::

Dev/DBMS

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

2003. 7. 23. 09:34
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
반응형

'Dev > DBMS' 카테고리의 다른 글

방화벽을 통해 SQL Server와 통신하는 데 필요한 TCP 포트  (1) 2003.09.01
xml -> sql  (2) 2003.08.29
MSSQL에서 동적SQL을 구현(OUTPUT파라미터 없을때)  (2) 2003.07.22
isql.exe를 이용한 sql문서를 실행해 보자  (0) 2003.07.18
mysql은 oledb안되남^^;;  (3) 2003.01.09
    'Dev/DBMS' 카테고리의 다른 글
    • 방화벽을 통해 SQL Server와 통신하는 데 필요한 TCP 포트
    • xml -> sql
    • MSSQL에서 동적SQL을 구현(OUTPUT파라미터 없을때)
    • isql.exe를 이용한 sql문서를 실행해 보자
    newtype
    newtype
    지극히 개인적인 지식의 창고

    티스토리툴바