Assignment 4 – Stored Functions

Objectives

To write a stored function that can be used in Queries and Stored Procedures

Stored functions, along with stored procedures, are one of the best tools to have in your SQL toolkit. They are incredibly useful for encapsulating commonly used SQL statements. They are also useful for shielding the inner details of the database from external development when databases are used in large systems.

Assignment

You will be writing 2 stored functions for this assignment. One function 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 a 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).

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. This is just an example of a use of a Stored Function.

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

A great article on Stored Functions returning Tables – http://www.sqlservertutorial.net/sql-server-user-defined-functions/sql-server-table-valued-functions/

and Scalar functions

https://www.sqlservertutorial.net/sql-server-user-defined-functions/sql-server-scalar-functions/

I

WiseOwl Video on Table-Valued Functions – https://www.youtube.com/watch?v=nCAEgNxC7nU

Grading Criteria

5 Points for creating and executing a stored function returning a scalar.
5 points for creating and executing a stored function returning a table.