User Defined Functions (UDFs) in SQL Server

User Defined Functions (UDFs)

User defined functions are routines that encapsulates SQL logic inside it. Like stored procedures User defined functions can also be passed input parameters but user defined functions are compiled and executed at runtime so pretty slower than stored procedures.

Certain limitations for User defined functions:

i)  UDF can’t perform DML (data manipulation language) operations like Insertion, Update and Deletion on the base table.

ii) UDF can’t return non deterministic values like GETDATE () etc.

iii) Stored procedure can’t be called from inside a UDF whereas a stored procedure can call a user defined function or another stored procedure inside it.

Syntax:

CREATE FUNCTION dbo.Function

     (

     /*

     @parameter1 datatype = default value,

     @parameter2 datatype

     */

     )

  RETURNS /* datatype */

  AS

     BEGIN

      /* sql statement ... */

     RETURN /* value */

     END

 

Type of UDF :

1) Scalar Functions   (returns a single value)

CREATE FUNCTION EmployeeContactID(@Empid int)

RETURNS int

AS

BEGIN 

Declare @returnvalue int

Select @returnvalue=Employee.ContactID from Employee where Employee.EmployeeID=@Empid

        RETURN   @returnvalue

END

 

Execution:

select dbo.EmployeeContactID(1)

 

2) Inline Functions   (returns a table)

CREATE FUNCTION dbo.GetEmployeeFunction(@empid int)

RETURNS TABLE

AS

RETURN SELECT *

FROM employee where employee.EmployeeID=@empid

 

Execution:

 

select * from dbo.GetEmployeeFunction(1)

Difference between Stored procedures and User defined functions:

i)A stored procedure is pre compiled while a User defined function is compiled and executed at runtime.

ii)A Stored procedure is more flexible than user defined function like you can write complex logic (for example exceptional handling using try catch block is possible in stored procedures which is not possible in user defined functions)

iii) A stored procedure can call another stored procedure or user defined function inside it but a user defined function can’t call stored procedure inside it.

iv)A stored procedure can return non deterministic values but a user defined function can’t return a non deterministic values like Get Date () function.

v) A user defined functions does not support DML operations like insertion, deletion and update on the base table but it is possible via stored procedure.

vi) A user defined function is easier to execute and can be used  inside selection and even for joins but stored procedure can’t be used inside selection queries and it can’t be used to join with other tables.