Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2003
    Posts
    41

    Unanswered: error no in dynamic sql

    to find @error in dynamic sql

    I have a temp table in my stored procedure. I tried inserting into the temp table thro dynamic sql. upto this is fine. Now i want to find if there is any error in my sql query. How to check this.

    i have added the code below

    CREATE PROCEDURE USP_RULE
    AS
    create table #TABLE1
    (
    SlNo int identity(1,1), EqNum varchar(25),Pointnum varchar(25)
    )
    declare @EqNum varchar(25),@Pointnum varchar(25)
    DECLARE @STRDBNAME VARCHAR(50)
    SET @STRDBNAME = 'DB1'
    EXEC('insert into '+#TABLE1+' select EQNUM,POINTNUM from '+@STRDBNAME+'..TABLE2')

    IF (@@ERROR > 0)
    BEGIN
    RAISERROR ('ERROR')
    END

    GO

  2. #2
    Join Date
    Jul 2002
    Location
    Village, MD
    Posts
    621
    You cannot use temporary table inside exec, try this idea:

    insert into #TABLE1
    EXEC('select EQNUM,POINTNUM from '+@STRDBNAME+'..TABLE2')
    Last edited by snail; 03-05-04 at 10:32.

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I'd suggest that you turn your EXEC into a SELECT just long enough to see what is worng (!).

    -PatP

  4. #4
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    I think jtamil2001 was referring to error checking, not that s/he is getting one already. SNAIL's suggestion is the one that the poster was after. And then, if INSERT fails, @@ERROR will contain the corresponding value. I'd also wrap it all into a transaction:

    begin tran
    insert into #TABLE1
    EXEC('select EQNUM,POINTNUM from '+@STRDBNAME+'..TABLE2')

    IF (@@ERROR > 0) BEGIN
    RAISERROR ('ERROR')
    rollback tran
    return (1)
    END
    commit tran
    Last edited by rdjabarov; 03-05-04 at 11:31.

  5. #5
    Join Date
    Jul 2002
    Location
    Village, MD
    Posts
    621
    Originally posted by rdjabarov
    I think jtamil2001 was referring to error checking, not that s/he is getting one already. SNAIL's suggestion is the one that the poster was after. And then, if INSERT fails, @@ERROR will contain the corresponding value. I'd also wrap it all into a transaction:

    begin tran
    EXEC('insert into '+#TABLE1+' select EQNUM,POINTNUM from '+@STRDBNAME+'..TABLE2')

    IF (@@ERROR > 0) BEGIN
    RAISERROR ('ERROR')
    rollback tran
    return (1)
    END
    commit tran
    Why it needs transaction for one command?

Posting Permissions

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