1. Write a script that declares and sets a variable that’s equal to the total outstanding balance
due. If that balance due is greater than $10,000.00, the script should return a result set
consisting of VendorName, InvoiceNumber, InvoiceDueDate, and Balance for each invoice
with a balance due, sorted with the oldest due date first. If the total outstanding balance due
is less than $10,000.00, return the message “Balance due is less than $10,000.00.”
2. The following script uses a derived table to return the date and invoice total of the earliest
invoice issued by each vendor. Write a script that generates the same result set but uses a
temporary table in place of the derived table. Make sure your script tests for the existence of
any objects it creates.
SELECT VendorName, FirstInvoiceDate, InvoiceTotal
FROM Invoices JOIN
(SELECT VendorID, MIN(InvoiceDate) AS FirstInvoiceDate
GROUP BY VendorID) AS FirstInvoice
ON (Invoices.VendorID = FirstInvoice.VendorID AND
Invoices.InvoiceDate = FirstInvoice.FirstInvoiceDate)
ON Invoices.VendorID = Vendors.VendorID
ORDER BY VendorName, FirstInvoiceDate
3. 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.
@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
4. Code three calls to the procedure created in problem 3 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
(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
SELECT VendorName, InvoiceNumber, InvoiceDueDate,
InvoiceTotal - CreditTotal - PaymentTotal AS Balance
FROM Vendors JOIN Invoices
ON Vendors.VendorID = Invoices.VendorID
WHERE InvoiceID = dbo.fnUnpaidInvoiceID()
Extra Credit (5 points):
1. Write a script that generates the same result set as the code shown in problem 2, but
uses a view instead of a derived table. Also write the script that creates the view. Make
sure that your script tests for the existence of the view. The view doesn’t need to be
redefined each time the script is executed.
2. Write a script that uses dynamic SQL to return a single column that represents the
number of rows in the first table in the current database. The script should automatically
choose the table that appears first alphabetically, and it should exclude tables named
dtproperties and sysdiagrams. Name the column CountOfTable, where Table is the
chosen table name.
Hint: Use the sys.tables catalog view.
3. Create a stored procedure named spDateRange that accepts two parameters,
@DateMin and @DateMax, with data type varchar and default value null. If called with
no parameters or with null values, raise an error that describes the invalid syntax. If
called with non-null values, validate the parameters. Test that the literal strings are valid
dates and test that @DateMin is earlier than @DateMax. If the parameters are valid,
return a result set that includes the InvoiceNumber, InvoiceDate, InvoiceTotal, and
Balance for each invoice for which the InvoiceDate is within the date range, sorted with
earliest invoice first.
4. Code a call to the stored procedure created in exercise 3 that returns invoices with an
InvoiceDate between April 10 and April 20, 2008. This call should also catch any errors
that are raised by the procedure and print the error number and description.
5. Create a table-valued function named fnDateRange, similar to the stored procedure of
exercise 3. The function requires two parameters of data type smalldatetime. Don’t
validate the parameters. Return a result set that includes the InvoiceNumber,
InvoiceDate, InvoiceTotal, and Balance for each invoice for which the InvoiceDate is
within the date range. Invoke the function from within a SELECT statement to return
those invoices with InvoiceDate between April 10 and April 20, 2008.
im using sql servers