LISTING 1

CREATE PROCEDURE dbo.pr_AddVendor
@Email [varchar](100),
@LastName [varchar](50),
@FirstName [varchar](50),
@UserID [char](35),
@MiddleName [varchar](50),
@VendorName [varchar](100),
@VendorCreditLine [int]
AS

DECLARE
@Users_Pk [int]
INSERT INTO
Users
(Email,
LastName,
FirstName,
UserID)
VALUES
(@Email,
@LastName,
@FirstName,
@UserID)
SET @Users_Pk = SCOPE_IDENTITY()
INSERT INTO
Vendors
(VendorName,
Users_Fk)
VALUES
(@VendorName,
@Users_Pk)
GO

LISTING 2

DECLARE
@Users_Pk [int]
BEGIN TRANSACTION
INSERT INTO
Users
(Email,
LastName,
FirstName,
UserID)
VALUES
(@Email,
@LastName,
@FirstName,
@UserID)
IF @@Error <> 0
BEGIN
ROLLBACK TRANSACTION
Return 1 - this sets the return code to something other than 0 to indicate the error.
END

-- If you set the return code to anything other than 0 the rest of the procedure will stop
SET @Users_Pk = SCOPE_IDENTITY()
INSERT INTO
Vendors
(VendorName,
Users_Fk)
VALUES
(@VendorName,
@Users_Pk)

IF @@Error <> 0
BEGIN
ROLLBACK TRANSACTION
Return 1 - this sets the return code to something other than 0 to indicate the error.
END

COMMIT TRANSACTION
Return 0
GO

LISTING 3

SET @ErrorMessage = ""

-- If you set the return code to anything other than 0 the rest of the procedure will stop
SET @Users_Pk = SCOPE_IDENTITY()
INSERT INTO
Vendors
(VendorName,
Users_Fk)
VALUES
(@VendorName,
@Users_Pk)

IF @@Error <> 0
BEGIN
ROLLBACK TRANSACTION
Return 1 - this sets the return code to something other than 0 to indicate the error.
SET @ErrorMessage = "The vendor insert failed."
END

COMMIT TRANSACTION
Return 0
GO