Scribd
Upload
23 views

Module - 6 Functions

Uploaded by

Santhosh Pa
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
23 views

Module - 6 Functions

Uploaded by

Santhosh Pa
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
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