Spring 2004
Due: 2004-04-09
Friday
PURPOSE:
The purpose of
this lab is to learn about Stored Procedures in SQL Server. This is a group submission. In other
words, your group needs to submit 1 copy, and you are free and encouraged to
collaborate within your group.
DESCRIPTION/IMPLEMENTATION:
Consider the query in Lab4.Query9.
Convert this query into the body of a stored procedure. The input
parameters are:
Store Name
Order Number
Author First Name
Author Last Name
Title
The default values for each of these input parameters are NULL.
The caller of the stored procedure can enter any combination of inputs,
including none. When the user enters no input values, the result (output)
should be identical to Lab4Query9.
e.g.
Use Case 1:
Store Name
= ‘Bookbeat’
Order
Number = ‘423LL930’
In this
case, the result should contain the following 2 rows:
|
Bookbeat |
423LL930 |
Abraham |
Bennet |
The Busy
Executive's Database Guide |
10 |
66.67 |
100 |
12.5 |
|
Bookbeat |
423LL930 |
Marjorie |
Green |
The Busy Executive's
Database Guide |
10 |
20 |
100 |
12.5 |
e.g.
Use Case 2:
Store Name
= ‘Fricative
Bookshop’
Author
First Name = ‘Marjorie’
In this
case, the result should contain the following 1 row:
|
Fricative
Bookshop |
X999 |
Marjorie |
Green |
You Can
Combat Computer Stress! |
35 |
70 |
100 |
58.33 |
NOTE: All character comparisons need to be
case-insensitive (HINT: Use UPPER or LOWER built-in function of SQL Server).
NOTE: Your procedure should work if (say) store name
contained single quotes. In other words, your procedure should work for any of
the input values that already exist in the lab4 database.
NOTE: For non-NULL input values, you need to check
if they exist. If not, return a user-friendly message containing the name that doesn’t
exist.
NOTE: Assume all required tables exist in catalog.
NOTE: You will have to import the lab4 database into
your group account. Create the procedure in that account.
NOTE: Provide adequate documentation inside the
procedure. State your assumptions clearly.
Submission:
Print a Word copy of the CREATE PROCEDURE and submit
in class. In addition, the TA will be logging into your group account, and
verifying various use cases as inputs.
Last Updated: 3/30/2004 10:09:28 AM