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)
*/