Scribd
Upload
23 views

Module - 6 Functions

Uploaded by

Santhosh Pa
Copyright
© © All Rights Reserved
Available Formats
Download as PDF, TXT or read online on Scribd
23 views

Module - 6 Functions

Uploaded by

Santhosh Pa
Copyright
© © All Rights Reserved
Available Formats
Download as PDF, TXT or read online on Scribd
You are on page 1/ 16

Implementing

User-Defined Functions
Overview

Introducing User-Defined Functions


Implementing User-Defined Functions
Lesson: Introducing User-Defined Functions

Types of User-Defined Functions


The CREATE FUNCTION Statement
The ALTER FUNCTION and DROP FUNCTION Statements
Types of User-Defined Functions

Scalar Functions
 Similar to a built-in function

Inline Table-Valued Functions


 Similar to a view with parameters
 Returns a table as the result of single SELECT statement

Multi-Statement Table-Valued Functions


 Content like a stored procedure
 Referenced like a view
The CREATE FUNCTION Statement

Creating a Function
USE Northwind
CREATE FUNCTION fn_NewRegion
(@myinput nvarchar(30))
RETURNS nvarchar(30)
BEGIN
IF @myinput IS NULL
SET @myinput = 'Not Applicable'
RETURN @myinput
END

Restrictions on Functions
The ALTER FUNCTION and DROP FUNCTION Statements

ALTER FUNCTION ALTER FUNCTION dbo.fn_NewRegion


(@myinput nvarchar(30))
 Retains assigned RETURNS nvarchar(30)
permissions BEGIN
IF @myinput IS NULL
 Causes the new SET @myinput = 'N/A'
function definition to RETURN @myinput
replace END
GO
existing definition

DROP FUNCTION dbo.fn_NewRegion


DROP FUNCTION GO
Lesson: Implementing User-Defined Functions

Scalar User-Defined Functions


Example of a Scalar User-Defined Function
Multi-Statement Table-Valued Functions
Example of a Multi-Statement Table-Valued Function
Inline Table-Valued Functions
Example of an Inline Table-Valued Function
Best Practices
Scalar User-Defined Functions

RETURNS Clause Specifies Data Type


Function Is Defined Within a BEGIN…END Block
Return Type Is Any Data Type Except text, ntext, image,
Can Be Invoked Anywhere a Scalar Expression of the
Same Data Type Is Allowed
Example of a Scalar User-Defined Function

Creating the Function


USE Northwind
CREATE FUNCTION fn_DateFormat
(@indate datetime, @separator char(1))
RETURNS Nchar(20)
AS
BEGIN
DECLARE @datestr Nchar(20)
SET @datestr =
CONVERT(Nvarchar(20), datepart(mm,@indate))
+ @separator
+ CONVERT(Nvarchar(20), datepart(dd, @indate))
+ @separator
+ CONVERT(Nvarchar(20), datepart(yy, @indate))
RETURN @datestr
END

Calling the Function Using a Parameter


SELECT dbo.fn_DateFormat(GETDATE(), ':')
Inline Table-Valued Functions

Content of the Function Is a SELECT Statement


Do Not Use BEGIN and END
RETURNS Specifies table as the Data Type
Format Is Defined by the Result Set
Example of an Inline Table-Valued Function

Creating the Function


USE Northwind
GO
CREATE FUNCTION fn_CustomerNamesInRegion
( @RegionParameter nvarchar(30) )
RETURNS table
AS
RETURN (
SELECT CustomerID, CompanyName
FROM Northwind.dbo.Customers
WHERE Region = @RegionParameter
)

Calling the Function Using a Parameter


SELECT * FROM fn_CustomerNamesInRegion(N'WA')
Multi-Statement Table-Valued Functions

BEGIN and END Enclose Multiple Statements


RETURNS Clause Specifies table Data Type
RETURNS Clause Names and Defines the Table
Example of a Multi-Statement Table-Valued Function

Creating the Function


USE Northwind
GO
CREATE FUNCTION fn_Employees
(@length nvarchar(9))
RETURNS @tbl_Employees TABLE
(EmployeeID int PRIMARY KEY NOT NULL,
[Employee Name] Nvarchar(61) NOT NULL)
AS
BEGIN
IF @length = 'ShortName'
INSERT @tbl_Employees SELECT EmployeeID, LastName
FROM Employees
ELSE IF @length = 'LongName'
INSERT @tbl_Employees SELECT EmployeeID,
(FirstName + ' ' + LastName) FROM Employees
RETURN
END

SELECT * FROM dbo.fn_Employees('LongName')


Calling the Function Or
SELECT * FROM dbo.fn_Employees('ShortName')
Best Practices

 Use Complex Scalar Functions on Small Result Sets

Use Multi-Statement Functions Instead of Stored


 Procedures That Return Tables

 Use Inline Functions to Create Parameterized Views

 Use Inline Functions to Filter Indexed Views


Lab A: Creating User-Defined Functions

Exercise 1: Creating a Scalar


User-Defined Function
Exercise 2: Creating a Multi-Statement
Table-Valued User-Defined Function
Exercise 3: Creating an Inline Table-Valued
User-Defined Function
Date Format

You might also like