on p.477 in Murach\'s SQL Server 2008 for developers 1. Create a stored procedur

Question

on p.477 in Murach's SQL Server 2008 for developers 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. 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 5.Create a scaler-valued function named fnUnpaidInvoiceID that returns InvoiceID of the earliest invoice with an unpaid balance. Test the function in the following SELECT statement: SELECT VendorName, InvoiceNumber, InvoiceDueDate, InvoiceTotal - CreditTotal - PaymentTotal AS Balance FROM Vendors JOIN Invoices ON Vendors.VendorID = Invoices.VendorID WHERE InvoiceID = dbo.fnUnpaidInvoiceID() 8.Create a trigger for the Invoices table that automatically inserts the vendor name and address for a paid invoice into a table named ShippingLabels. The trigger should fire any time the PaymentTotal column of the Invoices table is updated. The structure of the ShippingLabels table is as follows: CREATE TABLE ShippingLabels (VendorName varchar(50), VendorAddress1 varchar(50), VendorAddress2 varchar(50), VendorCity varchar(50), VendorState char(2), VendorZipCode varchar(20)) Use this UPDATE statement to test the trigger: UPDATE Invoices SET PaymentTotal = 67.92, PaymentDate = '2008-08-23' WHERE InvoiceID = 100

Explanation / Answer

CREATE PROC spBalanceRanges @VendorVar varchar(50) = '%', @BalanceMin money = 0, @BalanceMax money = 0 AS declare @NBalanceMax Money if @BalanceMax = 9 or @BalanceMax = 0 set @NBalanceMax = 999999999999 else set @NBalanceMax = @BalanceMax Select VendorName,InvoiceNumber,InvoiceTotal-CreditTotal - PaymentTotal AS Balance FROM Vendors JOIN Invoices ON Vendors.VendorID = Invoices.VendorID Where ((InvoiceTotal-CreditTotal - PaymentTotal) Between @BalanceMin AND @NBalanceMax) AND (VendorName LIKE @VendorVar) ORDER BY Balance

Related Questions

Navigate

• Browse (All)
• Browse O
• Subjects

---

---

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