Dynamic SQL.... - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Dynamic SQL....

Author

Topic

FilippoP
Starting Member

2 Posts

Posted - 2002-11-04 : 05:24:32

Ciao,

In a stored procedure, I have to execute two DynamicSql statements,
@SQL1 and @SQL2

EXEC sp_executesql @SQL1
EXEC sp_executesql @SQL2

Everything work fine, but what if @SQL2 fails for instance? Is the Result Code reliable? Because, during my tests, I noticed that when @SQL1 was Ok, @SQL2 failed, the return code was 0.

Any suggestions?

thanks a lot,

Filippo

nr
SQLTeam MVY

12543 Posts

Posted - 2002-11-04 : 07:07:03

declare @rc int, @error int

EXEC @rc = sp_executesql @SQL1
select @error = @@error
if @rc = 0 and @error = 0
EXEC sp_executesql @SQL2
select @error = @@error

if @rc = 0 and @error = 0
select 'ok'
else
select 'failed'

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

FilippoP
Starting Member

2 Posts

Posted - 2002-11-04 : 09:03:00

Thanks a lot!

royv
Constraint Violating Yak Guru

455 Posts

Posted - 2002-11-04 : 09:13:35

I'm curious, would this not work?

EXEC (@SQL1)
SELECT @error=@@ERROR

***************************************
Death must absolutely come to enemies of the code!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources