Stored procedure error handling

[dtwilliams]

OK, i'm trying to do some error checking on stored procedures and am
following the advise in Erland Sommarskog's 'Implementing Error
Handling with Stored Procedures' document.

Can anybody help with my stored procedures and why it keeps erroring at
the '-- Create new Address Detail stage'? The errorCode value that is
being return in my web app is 0, so i'm not even sure why it's even
raising the error!!

Rather than executing the INSERT INTO AddressDetail in my
CreateSupplier procedure and checking for errors, i'd like to be able
execute a CreateAddressDetail SP, so that i can reuse it throughout my
web app.

New suppliers must have a contact address associated with it, so if
there's an error creating the suppliers address, i need my
CreateSupplier stored procedure to ROLLBACK and not create the new
supplier. That's why i'm not doing two separate calls to the procedures
from my app code.

Any suggestions are most appreciated.

Many thanks

Dan Williams.

CREATE PROCEDURE CreateSupplier
@supplierName varchar(50),
@userId bigint,
@address varchar(50),
@town varchar(50),
@county varchar(50),
@postCode varchar(15),
@contactName varchar(50)
AS
BEGIN

DECLARE @newSupplierId as bigint
DECLARE @newAddressDetailId as bigint
DECLARE @errorCode as bigint

SET NOCOUNT ON

BEGIN TRAN

INSERT INTO Supplier
(supplierName, accOpenedBy, accOpenedDate)
VALUES (@supplierName, @userId, getDate())

SET @newSupplierId = SCOPE_IDENTITY()

-- Check for an error creating new supplier
SELECT @errorCode = @@ERROR
IF (@errorCode <> 0) BEGIN ROLLBACK TRAN RAISERROR ('Error creating
supplier',16,1) RETURN @errorCode END

-- Create new Address Detail
EXEC @errorCode = CreateAddressDetail @address, @town, @county,
@postCode, @contactName, @newAddressDetailId OUTPUT

SELECT @errorCode = coalesce(nullif(@errorCode, 0), @@error)

if @errorCode <> 0 BEGIN ROLLBACK TRAN RAISERROR ('Error creating
address. ErrorCode = %d',16, @errorCode) RETURN @errorCode END

COMMIT TRAN
SET NOCOUNT OFF
RETURN @newSupplierId

END
GO

CREATE PROCEDURE CreateAddressDetail
@address varchar(50),
@town varchar(50),
@county varchar(50),
@postCode varchar(15),
@contactName varchar(50),
@newAddressDetailId bigint OUTPUT

AS
BEGIN

-- Create new AddressDetail

DECLARE @errorCode as bigint

SET NOCOUNT ON

BEGIN TRAN

INSERT INTO AddressDetail
(address, town, county, postCode, contactName)
VALUES (@address, @town, @county, @postCode, @contactName)

SET @newAddressDetailId = SCOPE_IDENTITY()

-- Check for an error creating new address
SELECT @errorCode = @@ERROR
IF (@errorCode <> 0)
BEGIN
RAISERROR ('Error creating new address detail',16,1)
ROLLBACK TRAN
END
ELSE
COMMIT TRAN
SET NOCOUNT OFF
RETURN @newAddressDetailId
END
GO

[Mike Epprecht \(SQL MVP\)]

Hi

Look at http://www.sommarskog.se/error-handling-II.html

Regards
--------------------------------
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland

IM: mi**@epprecht.net

MVP Program: http://www.microsoft.com/mvp

Blog: http://www.msmvps.com/epprecht/

"dt********@hotmail.com" <da**********@newcross-nursing.com> wrote in
message news:11**********************@g14g2000cwa.googlegr oups.com...

OK, i'm trying to do some error checking on stored procedures and am
following the advise in Erland Sommarskog's 'Implementing Error
Handling with Stored Procedures' document.

Can anybody help with my stored procedures and why it keeps erroring at
the '-- Create new Address Detail stage'? The errorCode value that is
being return in my web app is 0, so i'm not even sure why it's even
raising the error!!

Rather than executing the INSERT INTO AddressDetail in my
CreateSupplier procedure and checking for errors, i'd like to be able
execute a CreateAddressDetail SP, so that i can reuse it throughout my
web app.

New suppliers must have a contact address associated with it, so if
there's an error creating the suppliers address, i need my
CreateSupplier stored procedure to ROLLBACK and not create the new
supplier. That's why i'm not doing two separate calls to the procedures
from my app code.

Any suggestions are most appreciated.

Many thanks

Dan Williams.

CREATE PROCEDURE CreateSupplier
@supplierName varchar(50),
@userId bigint,
@address varchar(50),
@town varchar(50),
@county varchar(50),
@postCode varchar(15),
@contactName varchar(50)
AS
BEGIN

DECLARE @newSupplierId as bigint
DECLARE @newAddressDetailId as bigint
DECLARE @errorCode as bigint

SET NOCOUNT ON

BEGIN TRAN

INSERT INTO Supplier
(supplierName, accOpenedBy, accOpenedDate)
VALUES (@supplierName, @userId, getDate())

SET @newSupplierId = SCOPE_IDENTITY()

-- Check for an error creating new supplier
SELECT @errorCode = @@ERROR
IF (@errorCode <> 0) BEGIN ROLLBACK TRAN RAISERROR ('Error creating
supplier',16,1) RETURN @errorCode END

-- Create new Address Detail
EXEC @errorCode = CreateAddressDetail @address, @town, @county,
@postCode, @contactName, @newAddressDetailId OUTPUT

SELECT @errorCode = coalesce(nullif(@errorCode, 0), @@error)

if @errorCode <> 0 BEGIN ROLLBACK TRAN RAISERROR ('Error creating
address. ErrorCode = %d',16, @errorCode) RETURN @errorCode END

COMMIT TRAN
SET NOCOUNT OFF
RETURN @newSupplierId

END
GO

CREATE PROCEDURE CreateAddressDetail
@address varchar(50),
@town varchar(50),
@county varchar(50),
@postCode varchar(15),
@contactName varchar(50),
@newAddressDetailId bigint OUTPUT

AS
BEGIN

-- Create new AddressDetail

DECLARE @errorCode as bigint

SET NOCOUNT ON

BEGIN TRAN

INSERT INTO AddressDetail
(address, town, county, postCode, contactName)
VALUES (@address, @town, @county, @postCode, @contactName)

SET @newAddressDetailId = SCOPE_IDENTITY()

-- Check for an error creating new address
SELECT @errorCode = @@ERROR
IF (@errorCode <> 0)
BEGIN
RAISERROR ('Error creating new address detail',16,1)
ROLLBACK TRAN
END
ELSE
COMMIT TRAN
SET NOCOUNT OFF
RETURN @newAddressDetailId
END
GO

[dtwilliams]

Er.... I already have. That's the article i referenced in my original
post.

[Jason_Schaitel]

I am not a transaction pro or anything but perhaps I can point you in
the right direction, more experienced developers may eventually be more
helpful.

I could be wrong but I think in your CreateAddressDetail proc you
should not have the commit inside the else and since you are using
CreateAddressDetail inside another transaction you may want to label
your transaction and monitor transcount.

I may be wrong on both points but it may pay to check either way.

[Jason_Schaitel]

I am not a transaction pro or anything but perhaps I can point you in
the right direction, more experienced developers may eventually be more
helpful.

I could be wrong but I think in your CreateAddressDetail proc you
should not have the commit inside the else and since you are using
CreateAddressDetail inside another transaction you may want to label
your transaction and monitor transcount.

I may be wrong on both points but it may pay to check either way.

[Erland Sommarskog]

dt********@hotmail.com (da**********@newcross-nursing.com) writes:

OK, i'm trying to do some error checking on stored procedures and am

INSERT INTO Supplier
(supplierName, accOpenedBy, accOpenedDate)
VALUES (@supplierName, @userId, getDate())

SET @newSupplierId = SCOPE_IDENTITY()

-- Check for an error creating new supplier
SELECT @errorCode = @@ERROR

No, you are checking for error an error when retrieving the value from
SCOPE_IDENTITY(). Which never fails, so you will always get 0.

@@error is set after *every* statement.

This is why I always write my code as:

INSERT INTO Supplier (supplierName, accOpenedBy, accOpenedDate)
VALUES (@supplierName, @userId, getDate())
SELECT @err = @@error IF @err <> 0 RETURN @err

And then there is a space to the next statement. That is, conceptually
I view the error-checking bit as part of the statment it belongs to.
Oh! So much easier this will be in SQL 2005!
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[DA Morgan]

Erland Sommarskog wrote:

Oh! So much easier this will be in SQL 2005!

Why do you say that?

It is horrible now and I don't see anything new coming to T-SQL
that will make it any less so.
--
Daniel A. Morgan
http://www.psoug.org
da******@x.washington.edu
(replace x with u to respond)

[xAvailx]

Erland is probably referring to exception handling

http://codebetter.com/blogs/raymond..../20/46560.aspx

Thx, BZ

[DA Morgan]

xAvailx wrote:

Erland is probably referring to exception handling

http://codebetter.com/blogs/raymond..../20/46560.aspx

Thx, BZ

Thanks ... hadn't seen that before.

Nice to see that they have finally copied what's been in Oracle and
other products for more than 15 years.
--
Daniel A. Morgan
http://www.psoug.org
da******@x.washington.edu
(replace x with u to respond)

[dtwilliams]

OK, thanks for all the replies.

In the end, it wasn't just the fact that i needed to set by @errorCode
immediately after.

The problem was my CreateAddressDetail stored procedure i execute from
my CreateSupplier procedure was returning a new @newAddressDetailId
scope identity, hence causing my @errorCode to be greater than zero and
raising an error. I now just rely on using an OUTPUT variable.

Cheers

Dan