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

GOYou cannot use temporary table inside exec, try this idea:

insert into #TABLE1
EXEC('select EQNUM,POINTNUM from '+@.STRDBNAME+'..TABLE2')|||I'd suggest that you turn your EXEC into a SELECT just long enough to see what is worng (!).

-PatP|||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|||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?