|
Home > Archive > Microsoft SQL Server forum > August 2005 > Stored procedure error handling
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
Stored procedure error handling
|
|
| dtwilliams@hotmail.com 2005-08-25, 11:24 am |
| 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(@err
orCode, 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\) 2005-08-25, 11:24 am |
| Hi
Look at http://www.sommarskog.se/error-handling-II.html
Regards
--------------------------------
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"dtwilliams@hotmail.com" < dan_williams@newcros
s-nursing.com> wrote in
message news:1124986139.362538.125920@g14g2000cwa.googlegroups.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(@err
orCode, 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@hotmail.com 2005-08-25, 1:23 pm |
| Er.... I already have. That's the article i referenced in my original
post.
| |
| Jason_Schaitel 2005-08-25, 8:24 pm |
| 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 2005-08-25, 8:24 pm |
| 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.
| |
| DA Morgan 2005-08-25, 8:24 pm |
| 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
damorgan@x.washington.edu
(replace x with u to respond)
| |
|
|
| DA Morgan 2005-08-25, 8:24 pm |
| xAvailx wrote:
> Erland is probably referring to exception handling
>
> http://codebetter.com/blogs/raymond...1/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
damorgan@x.washington.edu
(replace x with u to respond)
| |
| dtwilliams@hotmail.com 2005-08-30, 9:25 am |
| 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
|
|
|
|
|