database management 2

WK4 Exercise Otmit on Canvas as a sol file under Assignments -> WKA. Due Date: Sunday, Feb 7 by 11p. 1. Create a stored procedure called sprindiaxeices that accepts two parameters- @NameVendor of type varchar(100) and @BalanceLimit of type smalleoex. This procedure displays the venderdane laxRitenumber, JugiceRuedate and Balance of vendorsame with the name equal to @NameVendor and where the balance is greater than @balanceLimit. If you call/test the procedure with @NameVendor='Federal Express Corporation' and @BalanceLimit= 0, the following table will be displayed. VenderNams InvgiseNumber lovgiseRuedate Federal Express Corporation 963253264 2012-04-17 00:00:00 Balance 52.25 Federal Express Corporation 263253268 2012-04-20 00:00:00 59.97 Federal Express Corporation 263253270 2012-04-21 00:00:00 67.92 Federal Express Corporation 263253273 2012-04-21 00:00:00 30.75 ** Balance TotalGesitatak Battal Code two EXEC statements that call/test the saciadiaveises, procedure with the following arguments: a @Namevendor is 'Federal Express Corporation' and balanceLimit is o pass by name) b. @Namevendor is "Blue Cross' and @BalanceLimit is 100 (pass by position) 2. Create a procedure called sodelos Rut which accepts two optional parameters BalanceMin and @BalanceMax of type spalarex with default values of NULL. In the procedure, write a query that counts the total number of invoices in the invoices table where the balance is within the range of balanceMin and balanceMax and returns the count value (use RETURN as we don't need decimals) in the procedure, also add the following- if @balancem in I NULL, set Balance Min too. If @ Balance Maxs NULL or 0, set it to the maximum balance in the invoice table code two exec statements that call/test the procedure and print the return value for the following 3. No arguments are provided the return value should be 101 Balance Min is 500 and balance Maxit 1000 (The return value should be 2) 1. Create a stored procedure called spFindInvoices that accepts two parameters- @Name Vendor of type varchar(100) and @BalanceLimit of type smallmoney. This procedure displays the VendorName, InvoiceNumber, InvoiceDue Date and Balance of VendorName with the name equal to @Name Vendor and where the balance is greater than @BalanceLimit. If you call/test the procedure with @Name Vendor="Federal Express Corporation' and @BalanceLimit = 0, the following table will be displayed Vendor Name InvoiceNumber InvoiceDueD ate Balance** Federal Express Corporation 963253264 2012-04- 17 00:00:00 52.25 Federal Express Corporation 263253268 2012-04- 20 00:00:00 59.97 Federal Express Corporation 263253270 2012-04 21 00:00:00 67.92 Federal Express Corporation 263253273 2012-04- 21 00:00:00 30.75 **Balance = Invoice Total-Credit Total-Payment Total Code two EXEC statements that call/test the spFindInvoices procedure with the following arguments: a. @Name Vendor is 'Federal Express Corporation and @BalanceLimit is 0 (pass by name) b. @Name Vendor is Blue Cross' and @BalanceLimit is 100 (pass by position) 2. Create a procedure called spBalanceCount which accepts two optional parameters @Balance Min and @ BalanceMax of type smallmoney with default values of NULL. In the procedure, write a query that counts the total number of invoices in the Invoices table where the balance is within the range of @Balance Min and @ Balance Max and returns the count value (use RETURN as we don't need decimals). In the procedure, also add the following- if @ BalanceMin is NULL, set @ Balance Min to 0. If @BalanceMax is NULL or 0, set it to the maximum balance in the Invoices table. Code two EXEC statements that call/test the procedure and print the return value for the following: a. No arguments are provided (The return value should be 10) b. @ Balance Min is 500 and @BalanceMax is 1000 (The return value should be 2)