Stored Procedure Review - 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

Transact-SQL (2000)

Stored Procedure Review

Author

Topic

mk_garg20
Constraint Violating Yak Guru

343 Posts

Posted - 2004-10-07 : 02:07:20

Please tell me how i can handle fatal and non-fatal error in the stored procedure.


CREATE Procedure up_modify_invoice_status(
@JobNumber AS varchar(10),
@InvoiceNo AS varchar(12),
@InvoiceStatusID AS int
)
AS

DECLARE @myERROR int -- Local @@ERROR
       , @myRowCount int -- Local @@ROWCOUNT


SET NOCOUNT ON
SET ARITHABORT ON
SET XACT_ABORT ON


UPDATE JobInvoice
SET InvoiceStatusID=@InvoiceStatusID
WHERE JobNumber=@JobNumber AND
InvoiceNo=@InvoiceNo

SELECT @myERROR = @@ERROR, @myRowCount = @@ROWCOUNT


 IF @myERROR != 0 
	RETURN @myERROR
ELSE
	RETURN @myRowCount
GO

Thanks

mk_garg

mk_garg20
Constraint Violating Yak Guru

343 Posts

Posted - 2004-10-07 : 19:37:55

Everyone suggest dont use RETURN statement to return results
So here is slightly modified SP


CREATE Procedure up_modify_invoice_status(
@JobNumber AS varchar(10),
@InvoiceNo AS varchar(12),
@InvoiceStatusID AS int,
@myRowCount AS int OUTPUT
)
AS

DECLARE @myERROR int -- Local @@ERROR


SET NOCOUNT ON
SET ARITHABORT ON
SET XACT_ABORT ON


UPDATE JobInvoice
SET InvoiceStatusID=@InvoiceStatusID
WHERE JobNumber=@JobNumber AND
InvoiceNo=@InvoiceNo

SELECT @myERROR = @@ERROR, @myRowCount = @@ROWCOUNT


 IF @myERROR != 0 
	RETURN @myERROR
ELSE
	RETURN 0
GO

Thanks

mk_garg

dasu
Posting Yak Master

104 Posts

Posted - 2004-10-08 : 06:43:45

please make me clear
what do u mean by fatal and not fatal error say clearly what ur expecting from this.
regards
dg

mk_garg20
Constraint Violating Yak Guru

343 Posts

Posted - 2004-10-10 : 19:03:51

When there is "Fatal Error", sql server stops processing of script . where as in case "Non-Fatal error" sql server goes to next statement to process.

someone suggested, we can not do much in case of fatal error.

mk_garg

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources