FirstName

# CS327e

# Lab 5

Spring 2004

Due: 2004-04-09 Friday 8.30 a.m. in class and in your group account.

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