database management 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 @Balance Limit. 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-PaymentTotal 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 @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 @ 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. @BalanceMin is 500 and @ BalanceMax 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 @Balance Limit. 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-PaymentTotal 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 @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 @ 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. @BalanceMin is 500 and @ BalanceMax is 1000 (The return value should be 2)
