User Defined Function in Sql Server

==>> In this article I am going for taking about User Defined Function .It's type,use with example.
         
        Users can create their own functions and make use of it inside T-SQL statements in system
         in system database or in user defined database.    

Three types of user-defined functions : -

1. Scalar Functions
2. Inline Table-Valued Functions
3. Multistatement Table-Valued Functions . 

1. Scalar Functions 

         User defined scalar function returns single value in the result by function . It return   
         value defined in function by the user.

*For Example we use EMP table here.

SELECT * FROM dbo.EMP ORDER BY EID  

In this table we select the all record from the EMP table Now we apply all function on this table.

Now can create a UDF Scalar Function in this way:-


Create FUNCTION GetEmpNameLocation
(
@FirstName VARCHAR(50),
@Location VARCHAR(50)
)
RETURNS VARCHAR(101)
AS
BEGIN
RETURN (SELECT @FirstName+'  '+@Location );
END


output:-


SELECT dbo.GetEmpNameLocation(EName,Location) AS Name ,Salary FROM dbo.EMP

So you can see we get the result Name with adding location of EMP.




         2. Inline Table-Valued Functions

             User defined inline table-valued function Return a set of rows as TABLE data type. The 
             value of table variable should be derived from a single SELECT statement.

Create a Inline Table-Valued Function





Create function fnGetEmp()
returns Table
AS
RETURN  (SELECT  * from dbo.EMP)


Output:-



Select * from fnGetEmp()

So in this way we can use Inline Table-Valued Function. It return table data type in single select statement.







        3. Multistatement Table-Valued Functions

            Returns a set of rows as TABLE data type. Unlike Inline Table-Valued function. In this 
           table variable must be explicitly declared and defined whose value can be derived from a
           multiple sql statements.






Create a Multistatement Table-Valued Functions


Create function fnGetMulEmp()
returns @Emplpyee Table
(
EID int,
EmpName varchar(50),
Salary int
)
As
begin
 Insert @Emplpyee Select EID,EName,Salary from Emp;
--Update Operation
 update @Emplpyee set Salary=25000 where EID=1;
--Update only in @Emplpyee table not in the Main Emp table
return
end



Output:-




Select * from fnGetMulEmp()


SELECT * FROM dbo.EMP

In the result image you see in the table get by using  Multistatement Table-Valued Functions updated salary but affected on Main table.


Note:-

• Function returns only single value.
• Function can be nested up to 32 level.UDF can not returns XML Data Type.
• Function accepts only input parameters.
• UDF does not support Exception handling.
• Function can not perform  Insert, Update, Delete  on table table.
• UDF can have upto 1023 input parameters while a Stored Procedure can have upto 2100 input parameters.

Hope this will help you.