/*

# CS327e

# Lab 5 Solution

Spring 2004

/*

This is the solution for lab5 cs327e Spring, 2004.

The solution shown here is 1 of many possible solutions.

In doing this solution, the stress is on

- Importance of good documentation within the stored procedure

- Error Checking (Division by zero, upper case matching, NULL checking etc.)

- Good naming conventions and practices

- Clearly stating assumptions

- Checking for invalid conditions

*/

/*

*********************************************************************************************************************

PROCEDURE NAME: LAB5

PROCEDURE DESC:

This procedure (based on Lab4Query9), is used to return a set of metrics for a given set of input.

Based on the combinations of the input parameters different results are produced. If successful, a status of 0 is returned, else -1.

The default for all input parameters is NULL. If no input specified, the procedure will return exactly the same results as in Lab4Query9.

Lab4Query9:

For each (store, order, author, title) that was sold, compute the following:

a) Qty_Sold = (say N) = Total Quantity of this title sold in this order at this store.

b) Pct_Author_Qty_Sold = PAQS = (N/ (AQS)) * 100.00

Where AQS = Total quantity of titles sold that are authored by this author, across all orders and stores.

c) Pct_Order_Qty_Sold = POQS = (N/ (OQS)) * 100.00

Where OQS = Total quantity of titles sold in this order in this store.

d) Pct_Store_Qty_Sold = PSQS = (N/ (SQS)) * 100.00

Where SQS = Total quantity of titles sold in this store, across all orders for this store.

In other words, the resultant table will have the following 9 columns:

Store_Name, Order_Number, Author_First_Name, Author_Last_Name, Title, Qty_Sold, Pct_Author_Qty_Sold, Pct_Order_Qty_Sold, Pct_Store_Qty_Sold

PROCEDURE INPUT:

@Store_Name

@Order_Number

@Author_First_Name

@Author_Last_Name

@Title

PROCEDURE OUTPUT:

@Status

ASSUMPTIONS:

1) An author might exist in the database, but no sales have occurred for him/her.

2) It is possible for denominator for some metrics could be 0.00.

*********************************************************************************************************************

*/

/* Drop the procedure if it already exists */

IF EXISTS (SELECT name FROM sysobjects WHERE name = ‘LAB5’ AND type = ‘P’)

DROP PROCEDURE LAB5

GO

/* Header of the stored procedure */

CREATE PROCEDURE LAB5

/* Set the defaults to be NULL for all INPUT parameters */

@Store_Name nvarchar (255) = NULL,

@Order_Number nvarchar (255) = NULL,

@Author_First_Name nvarchar (255) = NULL,

@Author_Last_Name nvarchar (255) = NULL,

@Title nvarchar (255) = NULL,

@Status int OUTPUT

AS

BEGIN /* Begin Body of the stored procedure */

/*

The following SET statement is needed for ANSI compatibility for NULL values, single quotes, implicit transactions etc. For more details, see

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_set-set_2uw7.asp

*/

SET ANSI_DEFAULTS ON

/* Initialize the status to be SUCCESS */

SET @status = 0

/* ------------------------------- Check for NULL inputs and existence -------------------------- */

/* Check for Store being NULL, or its non-existence in the database */

IF @Store_Name IS NULL

BEGIN

SET @Store_Name = '%'

PRINT ‘No Store Name entered.’

END

ELSE

BEGIN

/* Check to see if Store exists */

IF UPPER (RTRIM (@Store_Name)) NOT IN

(

SELECT DISTINCT UPPER (RTRIM (stor_name))

FROM stores

)

BEGIN

PRINT ‘Store <‘ + @Store_Name + ‘> does not exist in the database.’

SET @status = -1 /* FAILURE */

RETURN

END

ELSE

BEGIN

PRINT ‘Store <‘ + @Store_Name + ‘> exists in the database.’

END

END

/* Check for Order Number being NULL, or its non-existence in the database */

IF @Order_Number IS NULL

BEGIN

SET @Order_Number = '%'

PRINT ‘No Order Number entered.’

END

ELSE

BEGIN

/* Check to see if Order exists */

IF UPPER (RTRIM (@Order_Number)) NOT IN

(

SELECT DISTINCT UPPER (RTRIM (ord_num))

FROM sales

)

BEGIN

PRINT ‘Order <‘ + @Order_Number + ‘> does not exist in the database.’

SET @status = -1 /* FAILURE */

RETURN

END

ELSE

BEGIN

PRINT ‘Order <‘ + @Order_Number + ‘> exists in the database.’

END

END

/* Check for Author First Name being NULL, or its non-existence in the database */

IF @Author_First_Name IS NULL

BEGIN

SET @Author_First_Name = '%'

PRINT ‘No Author First Name entered.’

END

ELSE

BEGIN

/* Check to see if Author First Name exists */

IF UPPER (RTRIM (@Author_First_Name)) NOT IN

(

SELECT DISTINCT UPPER (RTRIM (au_fname))

FROM authors

)

BEGIN

PRINT ‘Author First Name <‘ + @Author_First_Name + ‘> does not exist in the database.’

SET @status = -1 /* FAILURE */

RETURN

END

ELSE

BEGIN

PRINT ‘Author First Name <‘ + @Author_First_Name + ‘> exists in the database.’

END

END

/* Check for Author Last Name being NULL, or its non-existence in the database */

IF @Author_Last_Name IS NULL

BEGIN

SET @Author_Last_Name = '%'

PRINT ‘No Author Last Name entered.’

END

ELSE

BEGIN

/* Check to see if Author Last Name exists */

IF UPPER (RTRIM (@Author_Last_Name)) NOT IN

(

SELECT DISTINCT UPPER (RTRIM (au_lname))

FROM authors

)

BEGIN

PRINT ‘Author Last Name <‘ + @Author_Last_Name + ‘> does not exist in the database.’

SET @status = -1 /* FAILURE */

RETURN

END

ELSE

BEGIN

PRINT ‘Author Last Name <‘ + @Author_Last_Name + ‘> exists in the database.’

END

END

/* Check for Title being NULL, or its non-existence in the database */

IF @Title IS NULL

BEGIN

SET @Title = '%'

PRINT ‘No Title entered.’

END

ELSE

BEGIN

/* Check to see if Title exists */

IF UPPER (RTRIM (@Title)) NOT IN

(

SELECT DISTINCT UPPER (RTRIM (title))

FROM titles

)

BEGIN

PRINT ‘Title <‘ + @Title + ‘> does not exist in the database.’

SET @status = -1 /* FAILURE */

RETURN

END

ELSE

BEGIN

PRINT ‘Title <‘ + @Title + ‘> exists in the database.’

END

END

/* If all is well so far */

IF @status = 0

BEGIN /* ------------------------------- Get the results -------------------------- */

SELECT

Store_Name,

Order_Number,

Author_First_Name,

Author_Last_Name,

Title,

Qty_Sold,

Pct_Author_Qty_Sold,

Pct_Order_Qty_Sold,

Pct_Store_Qty_Sold

FROM

(

SELECT

st.stor_name AS Store_Name,

s.ord_num AS Order_Number,

a.au_fname AS Author_First_Name,

a.au_lname AS Author_Last_Name,

t.title AS Title,

sum(s.qty) AS Qty_Sold,

CONVERT (NUMERIC (5, 2),

((sum (s.qty)) * 1.00/NULLIF (max (AQS.Author_Qty_Sold), 0.00)) * 100.00) AS Pct_Author_Qty_Sold,

CONVERT (NUMERIC (5, 2),

((sum (s.qty)) * 1.00/NULLIF (max (OQS.Order_Qty_Sold), 0.00)) * 100.00) AS Pct_Order_Qty_Sold,

CONVERT (NUMERIC (5, 2),

((sum (s.qty)) * 1.00/NULLIF (max (SQS.Store_Qty_Sold), 0.00)) * 100.00) AS Pct_Store_Qty_Sold

FROM

sales s,

stores st,

titles t,

titleauthor ta,

authors a,

(

SELECT ta.au_id AS au_id, sum(s.qty) AS Author_Qty_Sold

FROM sales s, titles t, titleauthor ta

WHERE s.title_id = t.title_id AND

t.title_id = ta.title_id

GROUP BY ta.au_id

) AQS,

(

SELECT s.ord_num AS ord_num, sum(s.qty) AS Order_Qty_Sold

FROM sales s

GROUP BY s.ord_num

) OQS,

(

SELECT s.stor_id AS stor_id, sum(s.qty) AS Store_Qty_Sold

FROM sales s

GROUP BY s.stor_id

) SQS

WHERE

s.stor_id = st.stor_id AND

s.title_id = t.title_id AND

t.title_id = ta.title_id AND

ta.au_id = a.au_id AND

ta.au_id = AQS.au_id AND

s.ord_num = OQS.ord_num AND

s.stor_id = SQS.stor_id

GROUP BY

st.stor_name,

s.ord_num,

a.au_fname,

a.au_lname,

t.title

) lab4query9

WHERE

UPPER (RTRIM (Store_Name)) LIKE

UPPER (RTRIM (@Store_Name)) AND

UPPER (RTRIM (Order_Number)) LIKE

UPPER (RTRIM (@Order_Number)) AND

UPPER (RTRIM (Author_First_Name)) LIKE

UPPER (RTRIM (@Author_First_Name)) AND

UPPER (RTRIM (Author_Last_Name)) LIKE

UPPER (RTRIM (@Author_Last_Name)) AND

UPPER (RTRIM (Title)) LIKE

UPPER (RTRIM (@Title))

END /* ------------------------------- End Get the results -------------------------- */

END /* End Body of the stored procedure */

GO

/*

To execute the procedure, here is 1 use case

DECLARE @status int

exec LAB5 'bookbeat ', '423ll930', NULL, 'bennet', 'the busy executive''s database guide ', @status OUTPUT

PRINT 'Status = ' + convert(char(20), @status)

*/