To write a stored function that can be used in Queries and Stored Procedures
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. |
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 |
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.
Week 4 lectures and reference COP4709 Course Lectures By Week
I will be answering questions on the discussion board and common questions will be transferred to this area.
Differences and uses of stored procedures and stored functions - http://stackoverflow.com/questions/1179758/function-vs-stored-procedure-in-sql-server
5 Points for each successful stored function.