database managment 2

1. Create a stored procedure called spFindInvoices that accepts two parameters- @Name Vendor of type varchar(100) and @Balance Limit of type smallmoney. This procedure displays the VendorName, InvoiceNumber, InvoiceDueDate 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-CreditTotal-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 @Balance Limit is 100 (pass by position) ate WK4-Exercise Submit on Canvas as a.sql file under Assignments -> WK4. Due Date: Sunday, Feb 7 by 11p. 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, invoiceDueDate 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 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 @ BalanceMin and @ BalanceMax and returns the count value (use RETURN as we don't need decimals). In the procedure, also add the following- if @ Balance Min is NULL, set @Balance Min to 0. If @ Balance Max 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 @ Balance Max is 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 @Balance Limit of type smallmoney. This procedure displays the VendorName, InvoiceNumber, InvoiceDueDate 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-CreditTotal-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 @Balance Limit is 100 (pass by position) ate WK4-Exercise Submit on Canvas as a.sql file under Assignments -> WK4. Due Date: Sunday, Feb 7 by 11p. 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, invoiceDueDate 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 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 @ BalanceMin and @ BalanceMax and returns the count value (use RETURN as we don't need decimals). In the procedure, also add the following- if @ Balance Min is NULL, set @Balance Min to 0. If @ Balance Max 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 @ Balance Max is 1000 (The return value should be 2)