Scalar user defined functions

Writing Functions and Stored Procedures in SQL Server

Meghan Kwartler

IT Consultant

User defined functions (UDFs)

What?

Routines that

Can accept input parameters
Perform an action
Return result (single scalar value or table)

Why?

Can reduce execution time
Can reduce network traffic
Allow for Modular Programming

What is modular programming?

Software design technique
Separates functionality into independent, interchangeable modules
Allows code reuse
Improves code readability

Functions in recipes

Chapter Two Recipe for Modular programming4.jpg

Bake function input parameters

Chapter Two Recipe for Modular programming3.jpg

Scalar UDF with no input parameter

-- Scalar function with no input parameters
CREATE FUNCTION GetTomorrow()
    RETURNS date AS BEGIN
RETURN (SELECT DATEADD(day, 1, GETDATE()))
END

Scalar UDF with one parameter

-- Scalar function with one parameter
CREATE FUNCTION GetRideHrsOneDay (@DateParm date)
    RETURNS numeric AS BEGIN
RETURN (
  SELECT 
    SUM(
      DATEDIFF(second, PickupDate, DropoffDate)
    )/ 3600 
  FROM 
    YellowTripData 
  WHERE 
    CONVERT (date, PickupDate) = @DateParm
) END;

Scalar UDF with two input parameters

-- Scalar function with two input parameters
CREATE FUNCTION GetRideHrsDateRange (
  @StartDateParm datetime, @EndDateParm datetime
) RETURNS numeric AS BEGIN RETURN (
  SELECT 
    SUM(
      DATEDIFF(second, PickupDate, DropOffDate)
    )/ 3600 
  FROM YellowTripData 
  WHERE 
    PickupDate > @StartDateParm 
    AND DropoffDate < @EndDateParm
) END;

It's your turn to create UDFs!

Writing Functions and Stored Procedures in SQL Server