Posted by Erland Sommarskog on 12/15/06 16:26

Dan Guzman (guzmanda@nospam-online.sbcglobal.net) writes:
> Check the proc return code and raise an error with state 127 on failure.
> OSQL will then terminate. For example:
>
> DECLARE @ReturnCode int
> EXEC @ReturnCode = dbo.usp_TEST1
> IF @ReturnCode <> 0
> BEGIN
> RAISERROR('Procedure dbo.usp_TEST1 return code is %d', 16, 127,
> @ReturnCode)
> END
> GO
>
> DECLARE @ReturnCode int
> EXEC @ReturnCode = dbo.usp_TEST2
> IF @ReturnCode <> 0
> BEGIN
> RAISERROR('Procedure dbo.usp_TEST2 return code is %d', 16, 127,
> @ReturnCode)
> END

Even better is this check:

IF @Returcode <> 0 OR @@error <> 0

The procedure may not set a return code in case of errors, and there
are errors where the proceudure does not return a value at all. (More
precisely compilation error, in which case the procedure is terminated
and execution continues with the next statement.)

If Joel is on SQL 2005 he should of course use TRY CATCH, but since he
using OSQL, I assmue that he is on SQL 2000.



--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]