Content
- Introduction
- Scalar functions
- Inline table valued function
- 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)
- 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')
