| 
  • 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.

View
 

COP4709 Assignment 4A - Stored Functions

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

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.