In Northwind, is there a rewrite this to use Try-Catch block instead of the IF @
ID: 672312 • Letter: I
Question
In Northwind, is there a rewrite this to use Try-Catch block instead of the IF @@error method)? Im trying to create the AddSupplierProduct stored procedure that uses the @@error function to determine whether an error occurs when each INSERT statement is executed. If the error does occur, the transaction is rolled back.
CREATE PROCEDURE AddSupplierProduct
@CompanyName nvarchar (40) = NULL,
@ContactName nvarchar (40) = NULL,
@ProductName nvarchar (40) = NULL,
@CategoryID int = NULL,
@QuantityPerUnit nvarchar(20) = NULL,
@Discontinued bit = NULL
AS
BEGIN TRANSACTION
INSERT Suppliers (CompanyName, ContactName)
VALUES (@CompanyName, @ContactName)
IF @@error <> 0
BEGIN
ROLLBACK TRAN
RETURN
END
DECLARE @InsertSupplierID int
SELECT @InsertSupplierID=@@identity
INSERT Products (ProductName, SupplierID, CategoryID, QuantityPerUnit, Discontinued)
VALUES (@ProductName, @InsertSupplierID, @CategoryID, @QuantityPerUnit, @Discontinued)
IF @@error <> 0
BEGIN
ROLLBACK TRAN
RETURN
END
COMMIT TRANSACTION
Explanation / Answer
The Modified Procedure is here using Try - Catch
CREATE PROCEDURE AddSupplierProduct
@CompanyName nvarchar (40) = NULL,
@ContactName nvarchar (40) = NULL,
@ProductName nvarchar (40) = NULL,
@CategoryID int = NULL,
@QuantityPerUnit nvarchar(20) = NULL,
@Discontinued bit = NULL
AS
BEGIN TRY
BEGIN TRANSACTION
INSERT Suppliers (CompanyName, ContactName)
VALUES (@CompanyName, @ContactName)
DECLARE @InsertSupplierID int
SELECT @InsertSupplierID=@@identity
INSERT Products (ProductName, SupplierID, CategoryID, QuantityPerUnit, Discontinued)
VALUES (@ProductName, @InsertSupplierID, @CategoryID, @QuantityPerUnit, @Discontinued)
COMMIT TRANSACTION
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
BEGIN
ROLLBACK TRAN
END
END CATCH
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.