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?