Q&A

  • 스토어드 프로시져 인수에 배열 사용하기??
샬롬~

스토어드 프로시져 작성시..

'1','2','3', ....., 'N'의 인수를 넘겨줘야 할때..

CREATE PROCEDURE SP_ARRAYTEST
@arg_1 varchar(8)='',
@arg_2 varchar(8)='',
           :
           :
@arg_N varchar(8)='';
          
AS
BEGIN

이라고 선언을 한다면

상당히 비효율 적이고 코딩양도 많아질거 같아서..^^;

이런 방법말고 적당한 방법이 있나요???

아래의 방법이 적당한 방법인지 모르겠네요..


프로시져 내부에서 SQL문장을 스트링으로 조합해주고..
EXEC(@STRSQL)을 사용하는 방법이 더 호욜적일까요??

1안)
CREATE PROCEDURE SP_ARRAYTEST
@arg_LIST varchar(800)=''
AS
BEGIN
    DECLARE @SQL VARCHAR(3000);
    SET @SQL = '';
    


    SET @SQL = 'SELECT column1, column2, ... , columnn '
             + 'FROM DBName.UserName.TableName '
             + 'WHERE KeyColumn IN ('+@arg_LIST+')'
             + 'ORDER BY Column1, ...';
            
    EXEC(@SQL);
END;


2안)
CREATE PROCEDURE SP_ARRAYTEST
@arg_LIST varchar(8000)=''
AS
BEGIN
    SET @arg_LIST = REPLACE(@arg_LIST,'''','');

    -- 임시 테이블을 생성한다.
    CREATE TABLE #TB_ARGS (ARG VARCHAR(20), PRIMARY KEY (ARG));

    -- 임시테이블에 생성한 자료를 입력한다.
    WHILE CHARINDEX(',',@arg_LIST) > 0
    BEGIN
        INSERT INTO #TB_ARGS(ARG)VALUES(SUBSTRING(@arg_LIST,1,CHARINDEX(',',@arg_LIST) - 1));
        SET @arg_LIST = SUBSTRING(@arg_LIST,CHARINDEX(',',@arg_LIST) + 1, LEN(@arg_LIST));
    END;
    INSERT INTO #TB_ARGS(ARG)VALUES(@arg_LIST);

    SELECT column1, column2, ... , columnn
    FROM DBName.UserName.TableName
    WHERE KeyColumn IN ( SELECT ARG FROM #TB_ARGS)
                 :
                 :
    ORDER BY Column1, ...;

    
    DROP TABLE #TB_ARGS;

END;

이거 혼자 이상한짖 한거 아닌가 모르겠네..ㅡㅡ;

SET @SQL = 'SELECT * FROM TABLE WHERE COL = ''' + @ARG + ''' ';
EXEC(@SQL)

해준것과..

SELECT * FROM TABLE WHERE COL = @ARG;

로 처리한것중 어떤게 더 바람직한 방법인가요??

즐프~
0 좋아요!
이 게시물을
1  COMMENTS
  • Profile
    미소나눔 • 2003.05.16 08:52
    인수는 2개이며 하나는 구분자(작업구분)
    또 하나는 데이타문자열로 문자열내에서 문자/숫자 파싱해서 처리합니다.
    DataString 으로 들어노는 문자 80000자.. 이정도면 충분하겠지여..
    그리고 응용하면 여러가지 형태로 SQL 문 만들수도 있음
    참고하샘. 즐푸~~~

    CREATE PROC DetailSearch_WelfareFamily
    (
            @Separator        varchar(40),
            @DataString         varchar(8000)
    )
    AS
            DECLARE @I  INT
            DECLARE @CHK  varchar(4)
            DECLARE @START INT
            DECLARE @END   INT
            DECLARE @SECTION varchar(100)
            DECLARE @FIELD        varchar(150)
            DECLARE @SQL varchar(8000)
            DECLARE @SET INT


            SET @SET = 1
            SET @Start = 1
            SET @i = 1

            IF @Separator = 'TfrmWelfareFamily' BEGIN

                    SET @SQL =' SELECT A.wp_name, B.wp_id, B.wf_Address, B.wf_Phone, B.wf_familytype, B.wf_securty, B.wf_familynumber FROM welfareperson A, welfarefamily B WHERE A.wp_id = B.wp_id  AND  '

                    WHILE @i <= Len(@DataString) BEGIN
                            SELECT @Chk =  SUBSTRING(@DataString, @i, 1)
                            IF   @CHK = '|' BEGIN
                                    SET @End = @i
                                    SET @Section = SUBSTRING(@DataString, @Start, @End - @Start)
                                    SET @Start = @End + 1
                                    
                                    IF @Section <> '' BEGIN
                                            SELECT @Field = ( CASE @SET WHEN 1 THEN   'B.wf_indate BETWEEN  '+''''+@Section+''''
                                                                            WHEN 2 THEN   '  ' +                                                                 ''''+@Section+''''
                                                                               WHEN 3 THEN   'B.wf_Section1 = ' +                                  ''''+@Section+''''
                                                                            WHEN 4 THEN   'B.wf_Section2 = ' +                                 ''''+@Section+''''
                                                                            WHEN 5 THEN   'B.wf_familytype = ' +                                ''''+@Section+''''
                                                                            WHEN 6 THEN   'B.wf_desire = ' +                                         ''''+@Section+''''
                                                                            WHEN 7 THEN   'B.wf_securty = ' +                                         ''''+@Section+''''     END )

                                            SELECT @SQL = @SQL + @Field + ' AND '

                                 END -- IF NOT   ''

                               SET @SET = @SET + 1
                    
                            END -- IF '|'

                            SET @i = @i + 1

                    END -- WHILE
                    
                    SET  @SQL = SUBSTRING (@SQL, 1, Len(@SQL)-3)    -- 마지막 ADN   제거      
                    
    --                SELECT @SQL

                    EXEC (@SQL)

            END -- IF  



    실행
    EXEC DetailSearch_WelfareFamily 'TfrmWelfareFamily','1901-12-11|1905-05-29|||||거택(3종)|'  
←First Page
657 658 659 660 661 662 663 664 665 666
Last Page→
한델 로그인 하기