User defined Functions in SQL
User Defined Functions are compact pieces of Transact SQL code, which can accept
parameters, and return either a value, or a table. Data transformation and reference
value retrieval are common uses for functions. User Defined Functions enable the
developer or DBA to create functions of their own, and save them inside SQL Server.
Advantages of User Defined Functions
Before SQL 2000, User Defined Functions (UDFs), were not available. Stored Procedures
were often used in their place.
One of the advantages of User Defined Functions over Stored Procedures, is the fact
that a UDF can be used in a Select, Where, or Case statement. They also can be used
to create joins. In addition, User Defined Functions are simpler to invoke than
Stored Procedures from inside another SQL statement.
Disadvantages of User Defined Functions
User Defined Functions cannot be used to modify base table information. The DML
statements INSERT, UPDATE, and DELETE cannot be used on base tables.
Another disadvantage is that SQL functions that return non-deterministic values
are not allowed to be called from inside User Defined Functions.
GETDATE is an example of a non-deterministic function. Every time the function is
called, a different value is returned. Therefore, GETDATE cannot be called from
inside a UDF you create.
Types of User Defined Functions
There are three different types of User Defined Functions. Each type refers to the
data being returned by the function.
- Scalar functions return a single value.
- In Line Table functions return a single table variable that was created by a select
statement.
- The final UDF is a Multi-statement Table Function. This function returns a table
variable whose structure was created by hand, similar to a Create Table statement.
It is useful when complex data manipulation inside the function is required.
Example for Scalar-valued Function
CREATE FUNCTION dbo.DateOnly
(
@DateTime
datetime
)
RETURNS varchar(10)
AS
BEGIN
DECLARE @Output
varchar(10)
SET @Output =
CONVERT(
varchar(10),@DateTime,101)
RETURN @Output
END
To call the function, execute :
SELECT dbo.DateOnly(
GETDATE())
Example for Inline Table-valued Function
CREATE FUNCTION dbo.FindNamesBy
(
@Name
varchar(10)
)
RETURNS TABLE
AS
BEGIN
RETURN SELECT * FROM Employee
WHERE EmpName
LIKE
'%' + @Name
END
To use the above function, execute :
SELECT * FROM
dbo.FindNamesBy(
'Cherukuri')
Example for Multi statement Table-valued Function
CREATE FUNCTION dbo.MultiLineFunction
(
@Name
varchar(10)
)
RETURNS @Result
TABLE
(
Empname
varchar(20),
HireDate
datetime,
OnProbation
char(1)
)
AS
BEGIN
INSERT INTO @Result (EmpName, HireDate)
SELECT Empname, HireDate
FROM Employee
WHERE EmpName
LIKE
'%' + @Name
UPDATE @Result
SET OnProbation =
'N'
UPDATE @Result
SET OnProbation =
'Y'
WHERE HireDate <
'11/16/2002'
RETURN
END
To use the above function, execute :
SELECT EmpName,HireDate,OnProbation FROM dbo.MultiLineFunction(
'Ch')