Assignment 4 - Stored Functions
Objectives
To write a stored function that can be used in Queries and Stored Procedures
Assignment
You will be writing 2 stored functions for this assignment. One functions will be a table valued function (they will return a set of values), the other will be a Scalar function.
Function 1: func_GetMenuItemsForMenu - For this function you will pass the ID of the menu to the function. The function will find all MenuItems that are on the menu. It will return a table.
Function 2: func_GetMenuItemDescriptionForMenuItemID - This will operate as expected in the title. You will pass an MenuItemID to the function and it will return the Menu Item Description. This will return a scalar (varchar)
With these 2 functions you will demonstrate that you can create a single SQL Query or Stored Procedure that uses the function call to return the description of all menu items that appear on a given menu. Demonstrate this with a real query.
You will turn in a report that contains; (1) the text of the 2 functions (2) a screen capture of your query, and (3) the results of the query (doc or pdf format - pdf preferred).
|
NOTE You may substitute tables of your own design and write 2 function that meet the same requirements as these functions. The function must use multiple tables, one must return a table, the other a scalar (varchar or text). Please include design details of your tables if you use this option. If you do this you are expected to also include a discussion (purpose, design) about your database on the discussion boards.
|
Information
First create and test your functions - once complete start thinking about the final requirement - given a menu ID - returning a description of each item on the menu. What stored functions allow you to do is simplify very complex queries. To illustrate this let's look at a complex query that uses an embedded table. In the query below the section in red is a SELECT statement that returns a table and the results of that query are treated as a table that is queried in the outer SELECT. The inner query could easily be replaced by a stored function providing 2 advantages; (1) a more simple and easy to read primary query, and (2) the inner section can be reused in other queries.
|
SELECT Table1.Name,
Table1.email,
CONCAT(flx_races.race_name, ' ', flx_race_types.race_type) AS Race,
YEAR(flx_races.race_date) AS Year
FROM
(
SELECT flx_team_registration_racers.racer_id,
CONCAT(flx_racers.first_name, ' ', flx_racers.last_name) AS Name,
flx_racers.email_address AS email,
COUNT(flx_team_registration_racers.racer_id) AS 'Race Count'
FROM flx_team_registration_racers,
flx_racers
WHERE flx_team_registration_racers.racer_id = flx_racers.racer_id
GROUP BY flx_team_registration_racers.racer_id
HAVING COUNT(flx_team_registration_racers.racer_id) = 1
) AS Table1,
flx_team_registrations,
flx_races,
flx_team_registration_racers,
flx_racers,
flx_race_types
WHERE Table1.racer_id = flx_team_registration_racers.racer_id
AND flx_team_registration_racers.team_registration_id = flx_team_registrations.team_registration_id
AND flx_team_registrations.race_type_id = flx_race_types.race_type_id
AND flx_team_registrations.race_id = flx_races.race_id
GROUP BY flx_team_registration_racers.racer_id
ORDER BY flx_races.race_date ASC |
Estimated Completion Time
Allot a few hours for this (it took me about 15 minutes to complete). You should take some time to learn what are some uses of Stored Functions and Stored Procedures.
Supporting Lectures
Week 4 lectures and reference COP4709 Course Lectures By Week
Questions and Answers
I will be answering questions on the discussion board and common questions will be transferred to this area.
External Resources
Differences and uses of stored procedures and stored functions - http://stackoverflow.com/questions/1179758/function-vs-stored-procedure-in-sql-server
Grading Criteria
5 Points for each successful stored function.
Comments (0)
You don't have permission to comment on this page.