How JustAnswer Works:
  • Ask an Expert
    Experts are full of valuable knowledge and are ready to help with any question. Credentials confirmed by a Fortune 500 verification firm.
  • Get a Professional Answer
    Via email, text message, or notification as you wait on our site.
    Ask follow up questions if you need to.
  • 100% Satisfaction Guarantee
    Rate the answer you receive.
Ask Eljon Your Own Question
Eljon, Consultant
Category: Programming
Satisfied Customers: 595
Experience:  Founder of
Type Your Programming Question Here...
Eljon is online now
A new question is answered every 9 seconds

1.Create a stored procedure named spBalanceRange that accepts

This answer was rated:

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 vendor name, as shown in figure 14-5 of the text. @BalanceMin and @BalanceMax
are parameters used to specify the requested range of balances due. If called with no parameters,
the procedure should return all invoices with a balance due.

2.Code three calls to the procedure created in exercise 1 as follows:
(a) pass the parameters by position with @VendorVar='Z%' and no balance range
(b) pass the parameters by name with @VendorVar omitted and a balance range from $200 to $1000
(c) pass the parameters by position with a balance due that’s less than $200 filtering for vendors whose names begin with C or F

5.Create a scalar-valued function named fnUnpaidInvoiceID that returns the 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 for 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

Here is the link to the DB files.
Also, The SQL statements should not include Cursor, fetch status, or Balance cursor.
It is for a beginners SQL class and these advanced statements should not be needed.
Hi my name isXXXXX for using JustAnswer.

When do you need this?
Customer: replied 5 years ago.
OK It'll be ready by then.
Customer: replied 5 years ago.
Thank you, XXXXX XXXXX enjoyed working with you before. I tried to asked this question directly for you , but it kept giving me an error screen.
Thanks for attempting to request me. If I have any questions I'll let you know.
Customer: replied 5 years ago.
I still need the answers to the other 2 problems. Number 5 and 8.
My apologies. I forgot to zipped them up to one file.
Eljon and 3 other Programming Specialists are ready to help you
Customer: replied 5 years ago.