Lab 7

Created @May 8, 2025 4:17 PM

Tags

1. Introduction to Functions
What is a Function?
A Transact-SQL routine that:

Accepts parameters

Performs an action (e.g., calculations)

Returns a result (scalar or table)

2. Functions vs. Stored Procedures

Feature Function Stored Procedure

Optional (can return 0, 1, or many

Return Value Must return a value
values)

Parameters Must take at least 1 Can take none or many

Data Cannot perform

Can modify data
Modification INSERT/UPDATE/DELETE

Cannot be called in SQL

Call Location Can be called in SQL statements
statements directly

Usage Best for computations Best for business logic

3. Built-In Functions in SQL Server

Date and Time Functions

Function Parameters Description

Lab 7 1
 Adds a specified interval to a date, reuturns
DATEADD (datepart , number, date )
a datetime

( datepart , startdate ,
DATEDIFF Returns the difference between two dates
enddate )

Returns a string with the name of the

DATENAME (datepart , date )
specified date part
DATEPART (datepart , date ) Returns an integer for the specified date part
DAY (date ) Returns day from a date
MONTH (date ) Returns month from a date
YEAR (date ) Returns year from a date
GETDATE() - Returns current system date and time

Examples:

SELECT DATEADD(month, 1, '2006

-08-30');
SELECT DATEDIFF(mm, '2000-03-
01','2000-06-01');
SELECT DATENAME(month, '2011-
02-02');
SELECT DATEPART(month, '2011-
02-02');

Mathematical Functions

Function Example Result

ABS() SELECT ABS(-12) 12
POWER() SELECT POWER(2, 3) 8
ROUND() SELECT ROUND(1.23455, 3) 1.235

Meta Data Function

COL_LENGTH('table', 'column') – Returns the defined length of a column

Lab 7 2
 String Functions

Function Description
ASCII() ASCII code of the first character
LEN() Number of characters in a string
LOWER() Converts text to lowercase
SUBSTRING() Returns part of a string

4. User-Defined Functions (UDFs)

Types:
1. Scalar Functions

2. In-line Table-Valued Functions

3. Multi-Statement Table-Valued Functions

Restrictions:
Cannot return multiple result sets

Cannot return text , ntext , image , cursor , or timestamp

Cannot call a stored procedure

1. Scalar Functions
Return a single value (e.g., integer, string, date)

Syntax:

CREATE FUNCTION function_name (@param1 DataType)

RETURNS ReturnDataType
AS
BEGIN
DECLARE @Result ReturnDataType;

Lab 7 3
 SET @Result = (expression);
RETURN @Result;
END;

Example:

CREATE FUNCTION GetTotalSales (@ProductID int)

RETURN Money
AS
BEGIN
Declare TotalSales Money;

SELECT @TotalSales = SUM(UnitPrice * Quantity)

FROM OrderDetails
WHERE ProductID = @ProductID;

Return @TotalSales;
END;

Calling:
Used directly in SELECT , WHERE , etc.

SELECT dbo.GetTotalSales(1) AS TotalSales;

2. In-Line Table-Valued Functions (TVFs)

Return a table

Must use a single SELECT inside the RETURN

Used in the FROM clause of queries

Syntax:

Lab 7 4
 CREATE FUNCTION function_name (@param1 DataType)
RETURNS TABLE
AS
RETURN (
SELECT column1, column2
FROM TableName
WHERE condition
);

Example:

CREATE FUNCTION GetCutomersOrders(@CustomerID int)

RETURNS TABLE
AS
RETURN(
SELECT OrderID, OrderDate, ShipCountry
FROM Orders
WHERE CustomerID = @CustomerID;
);

Calling:
Called like a table:

--Syntax
SELECT * FROM dbo.function_name(param);

--Example
SELECT * FROM dbo.GetCustomersOrders(5);

3. Multi-Statement Table-Valued Functions

Returns a table from multiple statements

Lab 7 5
 Format and structure of the table must be defined inside the RETURNS clause

Syntax:

CREATE FUNCTION function_name (@param1 DataType)

RETURNS @ReturnTable TABLE (
column1 DataType,
column2 DataType
)
AS
BEGIN
INSERT INTO @ReturnTable
SELECT column1, column2
FROM TableName
WHERE condition;

RETURN;
END;

Example:

CREATE FUNCTION GetEmployeesNames(@length nvarchar(9))

RETURNS @fn_employees TABLE(
EmployeeID int PRIMARY KEY,
EmployeeName nvarchar(50)
)
AS
BEGIN
IF @length = 'Short Name'
INSERT @fn_employees
SELECT EmployeeID, LastName
FROM Employees;
ELSE IF @length = 'Long Name'
INSERT @fn_employees
SELECT EmployeeID, (FirstName + LastName)

Lab 7 6
 FROM Employees;
RETURN;
END;

Calling:
Used in FROM clause:

--Syntax
SELECT * FROM dbo.function_name(param);

--Example
SELECT * FROM dbo.GetEmployeesNames('Short Name');

🔧 Altering & Dropping UDFs

Alter Function:

ALTER FUNCTION dbo.function_name

-- new definition here

Drop Function:

DROP FUNCTION dbo.function_name;

Lab 7 7