• If you are citizen of an European Union member nation, you may not use this service unless you are at least 16 years old.

  • Whenever you search in PBworks, Dokkio Sidebar (from the makers of PBworks) will run the same search in your Drive, Dropbox, OneDrive, Gmail, and Slack. Now you can find what you're looking for wherever it lives. Try Dokkio Sidebar for free.


COP4709 Assignment 4A - Stored Functions

Page history last edited by Dr. Ron Eaglin 6 years ago

Assignment 4 - Stored Functions




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,


  CONCAT(flx_races.race_name, ' ', flx_race_types.race_type) AS Race,

   YEAR(flx_races.race_date) AS Year



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,


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,






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.