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 CreateAddressDe tail 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 @newAddressDeta ilId 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 = CreateAddressDe tail @address, @town, @county,
@postCode, @contactName, @newAddressDeta ilId 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 CreateAddressDe tail
@address varchar(50),
@town varchar(50),
@county varchar(50),
@postCode varchar(15),
@contactName varchar(50),
@newAddressDeta ilId 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 @newAddressDeta ilId = 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 @newAddressDeta ilId
END
GO
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 CreateAddressDe tail 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 @newAddressDeta ilId 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 = CreateAddressDe tail @address, @town, @county,
@postCode, @contactName, @newAddressDeta ilId 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 CreateAddressDe tail
@address varchar(50),
@town varchar(50),
@county varchar(50),
@postCode varchar(15),
@contactName varchar(50),
@newAddressDeta ilId 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 @newAddressDeta ilId = 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 @newAddressDeta ilId
END
GO
Comment