2.Code three calls to the procedure created in exercise 1: a. passed by position

Question

2.Code three calls to the procedure created in exercise 1: a. passed by position with @VendorVar='Z'% and no balance range b. passed by with @VendorVar omitted and a balance range from $200 to $1000 c. passed by position with a balance due that's less than $200 filtering for vendors whose names begin with C or F ============================================================================================ EXERCISE 1 FOR REFERENCE(don't need it solved): 1. Create a stored procedure named spBalanceRange that accepts three optional parameters. The procedure returns a result set consisting of VendorName, InvoiceNumber, and Balance for each invoice with a balance due, sorted with largest balance due first. The parameter @VendorVar is a mask that's used with a LIKE operator to filter by that vendor name, as shown in figure 14-5. @BalanceMin and @BalanceMax are parameters used to specify the requested range of balances due. If called with no parameters or with a maximum value of 0, the procedure should return all invoices with a balance due.

Explanation / Answer

Procedure Calling and Creating ::::: CREATE PROC spBalanceRange @VendorVar VARCHAR(50) = NULL, AS DECLARE @QUERY VARCHAR(1000) SET @QUERY = 'SELECT VendorName, InvoiceNumber, PaymentTotal AS Balance FROM Invoices JOIN Vendors ON Invoices.VendorID = Vendors.VendorID ' IF @VendorVar IS NOT NULL SET @QUERY = @QUERY + 'WHERE VendorName LIKE ' + @VendorVar + ') ' SET @QUERY = @QUERY + ' ORDER BY PaymentTotal DESC' EXEC (@QUERY)

Related Questions

Navigate

• Browse (All)
• Browse 2
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.