Reference no: EM13854419

Part -1:

Question 1:

Rewrite this stored procedure to use Try-Catch block instead of the IF @@error method).

This example creates the AddSupplierProduct stored procedure that uses the @Oerror function to determine whether an error occurs when each INSERT statement is executed. If the error does occur, the transaction is rolled back.

https://queriesmssql.wordpress.comicategory/ad-hoc-queries/page/4/
USE Northwind
GO

CREATE PROCEDURE AddSupplierProduct
@CompanyName nvarchar (40) NULL,
@ContactName nvarchar (40) NULL,
@ProductName nvarchar (40) NULL,
@CategorylD 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 @InsertSupplierlD int
SELECT @InsertSupplierlDm@@identity
INSERT Products (ProductName, SupplierlD, CategorylD, QuantityPerUnit, Discontinued) VALUES (@ProductName, @InsertSupplierlD, @CategorylD, @QuantityPerUnit, @Discontinued)

IF @@error <> 0
BEGIN
ROLLBACK TRAN
RETURN
END
COMMIT TRANSACTION
/*
Note:

SupplierlD is auto-generated (Identity property is set to True) so it is not needed.

All the columns in the Suppliers table with the exception of CompanyName allow a null value.

All the columns in the Products table with the exception of ProductlD allow a null value including the two Foreign Keys of SupplierlD and CategorylD. If a SupplierlD or a CategorylD is given then it must exist in the corresponding table. The SupplierlD in this case is picked up from the @InsertSupplierlD. */

Question 2:

Create a stored procedure that allows me to enter the CustomerlD as a parameter and returns the CustomerlD and CompanyName.

Question 3:

The option is only needed if it is possible the query plan needed to be updated each time the stored procedure is executed.
• update
• re-execute
• recompile

• return

Part -2:

Create the following stored procedure (from Chapter 15 Exercise 1):
CREATE PROC spBalanceRange @VendorVar varchar(50) = 196', @BalanceMin money = 0, @BalanceMax money = 0
AS
SELECT VendorName
, InvoiceNumber
, InvoiceTotal - CreditTotal - PaymentTotal AS Balance
FROM Vendors JOIN Invoices ON Vendors.VendorlD = Invoices.VendorlD
WHERE VendorName LIKE @VendorVar
AND (InvoiceTotal - CreditTotal - PaymentTotal) BETWEEN @BalanceMin AND @BalanceMax ORDER BY Balance DESC;

Question 1:

EXEC spBalanceRange 'M%'

What does the exec above produce in terms of results. If you wanted companies that had 'Corp' in their name how would you code the EXEC statement?

Question 2:

EXEC spBalanceRange @BalanceMin = 200, @BalanceMax = 1000

What does the exec above produce in terms of results. What would happen it you miss coded the maximum balance to be 100 instead?

Question 3:

EXEC spBalanceRange [C,F]%, 0, 200

What does the exec above produce in terms of results. How would you code an EXEC statement to bring back those vendors whose name begin with the letter A thru L with a minimum balance of 100 and a maximum balance of 500?