User Defined Function (UDF) In SQL SERVER

Content

1. Introduction
2. Scalar functions
3. Inline table valued function
4. Multi-statement table valued functions

Introduction

• SQL server provides list of many predefined functions that are built in to the T-SQL language
• The main benefit of UDF is that we are not just limited to SQL provided functions.
• We can write our own functions to meet our specific needs or to simplify complex SQL codes.

Types of User Defined Functions

• SQL Server supports two types of User Defined Functions
• Scalar Functions – The function which returns a Scalar/Single value
• Table Valued Functions – The function which returns a row set of SQL server Table datatype. Table Valued Functions can be written as 
• Inline Table
• Multi-statement Table

1. Scalar Function

• They are like standard built in functions provided with SQL Server.
• They return scalar values that can be used anywhere a constant expression can be used.
• They typically take one or more arguments and returns a value of a specified data types.
• Every T-SQL function must return a result using the RETURN statement.

Syntax

CREATE FUNCTION fnName ([param,..])

RETURNS DataType AS

BEGIN

Statement RETURN DataType

END

Example

CREATE FUNCTION [dbo].[fnResult](@score int)

RETURNS VARCHAR(1) 

BEGIN 

DECLARE @s VARCHAR(1)

IF @score <50

SET @s='F'

ELSE IF @score <60

SET @s= 'E'

ELSE

SET @s= 'A'

RETURN @s

END

Call Function 

DECLARE @result CHAR(1)

SET @result=dbo.fnResult(50)

SELECT @result

2. Table Value Function

• A table-valued user-defined function returns a rowset instead of a single scalar value.
• Can be invoked in the FROM clause of a SELECT statement, just as we would a table or view.
• A table-valued function can almost be thought of as a view that accepts parameters, so the result set is determined dynamically.
• A table valued function specifies the keyword TABLE in its RETURNS clause.

a) Inline table valued function

• An inline table-valued function specifies only the TABLE keyword in the RETURNS clause, Without table definition information.
• The code inside the function is a single RETURN statement that invokes a SELECT statement.

Syntax

CREATE FUNCTION fName([param],..)

RETURNS TABLE

AS

RETURN (SELECT Statement)

Example

CREATE FUNCTION fnResultList ()

RETURNS TABLE

AS

RETURN (SELECT score.id,student.Studentname,[subject].[name] as Subjects,score,

[dbo].[fnResult](score) as result 

FROM SCORE inner join Student on score.studentid=student.StudentId 

inner join [Subject] on score.subjectid=[subject].id)

b) Multi statement table valued function

• Multi-statement table-valued functions differ from inline functions in two major ways
• The RETURNS clause specifies a table variable and its definition.
• The body of the function contains multiple statements, at least one of which populates the table variable with data values.

• The scope of the table variable is limited to the function in which it is defined.
• Within the function in which a table variable is defined, that table variable can be treated like a regular table. You can perform any SELECT, INSERT, UPDATE, or DELETE statement on the rows in a table variable, except for SELECT INTO.

Syntax

CREATE FUNCTION fName([param]..)
RETURNS @tableName TABLE (field DataType,…)
AS
BEGIN
INSERT INTO @tableName
SELECT field,..
FROM tableName
RETURN

END

Example

CREATE FUNCTION fnResultListWithCondition(@status CHAR(10))
RETURNS @table TABLE (id INT,studentname VARCHAR(50),[subject] VARCHAR(50),
score INT,Result CHAR(1))
AS

BEGIN
IF @status='pass'
BEGIN
INSERT INTO @table
SELECT score.id,student.Studentname,[subject].[name] as Subjects,score,
[dbo].[fnResult](score) as result
FROM SCORE inner join Student on score.studentid=student.StudentId
inner join [Subject] on score.subjectid=[subject].id
WHERE score.score>=50
END

ELSE IF @status='fail'
BEGIN
INSERT INTO @table
SELECT score.id,student.Studentname,[subject].[name] as Subjects,score,
[dbo].[fnResult](score) as result
FROM SCORE inner join Student on score.studentid=student.StudentId
inner join [Subject] on score.subjectid=[subject].id
WHERE score.score<50
END
ELSE
BEGIN
INSERT INTO @table
SELECT score.id,student.Studentname,[subject].[name] as Subjects,score,
[dbo].[fnResult](score) as result
FROM SCORE inner join Student on score.studentid=student.StudentId
inner join [Subject] on score.subjectid=[subject].id
END
RETURN
END

Call Function

SELECT * FROM [dbo].[fnResultListWithCondition] ('fail')
SELECT * FROM [dbo].[fnResultListWithCondition] (‘pass')
SELECT * FROM [dbo].[fnResultListWithCondition] (’all')