Results 1 to 2 of 2
  1. #1
    Join Date
    Sep 2005
    Posts
    20

    Unanswered: insertion to a dynamic generated table in a stored procedure

    Hi ,

    i wanted to know if we can insert into a table that is passed as a parameter
    for a stored procedure.

    here is sample code .FOR THE BELOW CODE I am getting an error at @TABLENAME while compilation

    CREATE PROCEDURE ANNUAL_ARCH (@TABLENAME CHAR(40), @YEAR CHAR(4))
    AS
    BEGIN

    INSERT INTO @TABLENAME (
    DIER_CDE,ACCT_NAM,ACCT_NUM,
    CAS_CAT,CAS_GRP,
    CAS_TXN_CDE,CAS_TXN_DESC,
    CLNT_NAM,CLNT_NUM,
    CLNT_REF_NUM,CURNCY_CDE,
    DR_CR,GFT_IND,GFT_REF_NUM,
    INSTR_METH_IND,MANL_IND,
    PAY_RECV_IND,
    SECR_NAM,SECR_NUM,
    STMNT_DTE,SYS_ORIG,
    TRD_DTE,TXN_AMT,TXN_DESC,
    USD_PAY_STMT_CDE,VAL_DTE,IMPRT_IND )
    SELECT DIER_CDE,ACCT_NAM,ACCT_NUM,
    CAS_CAT,CAS_GRP,
    CAS_TXN_CDE,CAS_TXN_DESC,
    CLNT_NAM,CLNT_NUM,
    CLNT_REF_NUM,CURNCY_CDE,
    DR_CR,GFT_IND,GFT_REF_NUM,
    INSTR_METH_IND,MANL_IND,
    PAY_RECV_IND,
    SECR_NAM,SECR_NUM,
    STMNT_DTE,SYS_ORIG,
    TRD_DTE,TXN_AMT,TXN_DESC,
    USD_PAY_STMT_CDE,VAL_DTE,IMPRT_IND

    FROM TFM820_CSH_TRN_HIST

    WHERE SUBSTRING(TFM820_CSH_TRN_HIST.STMNT_DTE,1,6)
    BETWEEN @YEAR+"01" AND @YEAR+"12"

    END

  2. #2
    Join Date
    Feb 2002
    Location
    Willy is on vacation
    Posts
    1,208
    NO

    Create the sql dynamically instead and use execute immediate feature.

    CREATE PROCEDURE ANNUAL_ARCH (@TABLENAME CHAR(40), @YEAR CHAR(4))
    AS
    BEGIN

    declare @cmd varchar(255)
    select @cmd = "INSERT INTO " + @TABLENAME + " (DIER_CDE,ACCT_NAM,ACCT_NUM,CAS_CAT,CAS_GRP, blah...)
    exec (@cmd)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •