Posted by Mike Epprecht \(SQL MVP\) on 08/25/05 19:19

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@newcross-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(@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
>

[Back to original message]