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 table.
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.
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 table.
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 * FROMdbo.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')
