User Defined Functions In SQL Server

Hello friends today I am going to talk about user defined functions in SQL Server. In SQL Server 2000, Microsoft introduced this feature of defining user defined functions. We can encapsulate our T-SQL code with in a user defined function that will perform its intended action for it was created.In SQL Server, we can define three types of user defined functions, which are following.

Types of Functions :- 

• Scalar user defined function
• Inline table value user defined function
• Multi-Statement table value user defined function

Scalar user defined function:- A scalar user defined function can returns any scalar data type except ntext, text,image and timestamp data types.

Syntax for defining scalar user defined function :-

CREATE FUNCTION GET_SUM(@FIRSTPARAM AS NUMERIC(10,2),@SECONDPARAM AS NUMERIC(10,2))
RETURNS NUMERIC(20,2)--We have to define the return type of function here(Must)
BEGIN
DECLARE @SUMVALUE AS NUMERIC(20,2)-->Declared variable to assign sum value
SET @SUMVALUE =@FIRSTPARAM +@SECONDPARAM -->Assigning  sum value
RETURN @SUMVALUE-->Last statement in the function would always be return statement.
END

Lets execute this function and see what is the outcome. In the below image you can see that I have written select then name of the function that we have just created and passed two values 10 and 23.4, but when I run this statement I get below error message encircled with blue color.

Note:-To execute scalar functions we need to specify fully qualified name of the functions
that meansincluding the owner of the object. For example in the above case we will write
"Select dbo.Get_Sum(10,23.4)"  

In the below window, we can see the result(encircled by red color)

Inline table value user defined function :- Inline table function returns a table data type and it cannot have multiple T-SQL statement in it. It doesn't support function body started with begin statement and end with End statement.

Syntax for defining Inline table value user defined function :- 

Create function Get_Vehicle_detail(@Eid as int)

returns table-->return type is table

as-->after 'as' begin statement is missing.

return(

Select emp.Eid,name, VehicleName from Emp 

inner join VehicleDetail on emp.Eid =VehicleDetail.Eid

where Emp.Eid=@Eid 

  ) 'End is also missing

We can see in the below script begin and End statement is missing, In the body of function there is only return statement where we are returning a result set of joining two tables Emp and VehicleDetail.Lets See what happen, if we try to create inline function with begin and end statement.

You can see that we are getting error message if we use Begin and End statement in Inline table value function.Hence the correct syntax of creating Inline table value function is the highlighted with the green color.Lets execute the created function.

Select * from Get_Vehicle_detail(101) -->We are passing eid =101 as a input parameter.

Multi-Statement table value user defined function:- Multi-Statement table value function is quite similar to Inline function with few exceptions. There exceptions are following.

• In Inline functions, the return type is a table, In Multi-Statement table value function, return type is also a table but we have to define the table structure in the return statement.
• We can have multiple statements in the body of Multi-Statement table value function while in Inline function we cannot have multiple statement within the function.
• In Inline function we do not define the body with Begin and End statement while in Multi-Statement functions we define the body of function with Begin and End statement.

Syntax for defining Multi-Statement table value user defined function :-

Create function Get_Vehicle_detail()

returns @tmp table(Eid int,Name varchar(20),VehicleName varchar(20))-->return statement with table def.

as

begin-->Begin statement

insert into @tmp select emp.Eid,name, VehicleName from Emp 

inner join VehicleDetail on emp.Eid =VehicleDetail.Eid

return

end -->End Statement

You can see that return type is a table variable with the table structure definition. and function body is started with Begin and end with End statement. Below is the result of executing the function.

I hope this article will help you to get a clear understanding about functions in SQL Server.There are few more things related to functions that I am going to cover in my forthcoming posts. Till then take care and have good day.