By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,199 Members | 1,725 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,199 IT Pros & Developers. It's quick & easy.

Stored procedure error handling

P: n/a
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

Aug 25 '05 #1
Share this Question
Share on Google+
9 Replies


P: n/a
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

Aug 25 '05 #2

P: n/a
Er.... I already have. That's the article i referenced in my original
post.

Aug 25 '05 #3

P: n/a
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.

Aug 25 '05 #4

P: n/a
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.

Aug 25 '05 #5

P: n/a
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

Aug 25 '05 #6

P: n/a
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)
Aug 25 '05 #7

P: n/a
Erland is probably referring to exception handling

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

Thx, BZ

Aug 25 '05 #8

P: n/a
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)
Aug 26 '05 #9

P: n/a
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

Aug 30 '05 #10

This discussion thread is closed

Replies have been disabled for this discussion.