Murach’s SQL for SQL Server – Exercise 14 – Guaranteed 100% score
1. Create a stored procedure in the AP database named spWhichTable that accepts a
column name and returns the name of the table or tables that have a column by that
name. Code a statement that calls the procedure.
2. 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.
3. Code three calls to the procedure in exercise 2:
(1) passed by position with @VendorVar=’Z%’ and no balance range
(2) passed by name with @VendorVar omitted and a balance range from $200 to
$1000
(3) passed by position with a balance due that’s less than $200 filtering for vendors
whose names begin with C or F
4. 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, the procedure should return an error message
describing the 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.
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()
6. Create a table-valued function named fnDateRange, similar to the stored procedure
of exercise 4. 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, 2002.
7. Use the function you created in exercise 6 in a SELECT statement that returns five
columns: VendorName and the four columns returned by the function.
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
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))
STATUS
9. A column that accepts null values but has a unique constraint can only have a single
row with a null value. Write a trigger that prohibits duplicates, except for nulls. Use
the following table. If an INSERT or UPDATE statement creates a duplicate value in
the NoDupName column, roll back the statement and return an error message.
CREATE TABLE TestUniqueNulls
(RowID int IDENTITY NOT NULL,
NoDupName varchar(20) NULL)
