Hey folks till now we have discussed Kill Processes in sql server 2008, View in sql  , default constraint in sql , Remove Cross duplicate rows in SQL, Recursive SQL Query , Recursive SQL Query-2, STUFF and CONCAT in SQL, RANK in SQL server , Difference between temporary table and table variable in sql server, UNIQUEIDENTIFIER in SQL Server, RAW Mode with FOR XML , AUTO Mode with FOR XML, EXPLICIT Mode with FOR XML , PATH Mode with FOR XML, OUTPUT Clause in SQL Server, Difference between delete and truncate in sql server etc.

Today we will discuss regarding User defined functions in sql.

There are three types of user defined functions in sql as below.

1. Scalar Function
2. Inline Table-Valued Function
3. Multi-Statement Table-Valued Function

Before we proceed to each of this function in detail lets first have a look at the pros and cons of user defined function.

1. We can not use INSERT, UPDATE or DELETE or any action that change the database state in UDFs.
2. We can not use OUTPUT INTO clause that has a table as its target.
3. UDFs can not return multiple result sets.
4. We can not use error handling in UDFs. It does not support TRY…CATCH, @ERROR or RAISERROR.
5. UDFs can not call a store procedure but a store procedure can call UDF.
6. Dynamic SQL or temp tables are not allowed in UDFs while table variables are allowed.
7. The FOR XML clause is not allowed.
8. UDFs must always return a value either a scalar value or a table.
9. UDFs doesn’t allow Transaction Management.

1. Scalar user defined function

A scalar function is a user defined function which will return single value. Following function will accept department id as a integer and returns department name as a output.

CREATE FUNCTION [dbo].[ufnGetDepartmentName](@DepartmentId [tinyint])
RETURNS [nvarchar](50)
AS
BEGIN
    DECLARE @ret [nvarchar](50);

    SET @ret =
        CASE @DepartmentId
            WHEN 1 THEN N'Research and Development'
            WHEN 2 THEN N'Sales and Marketing'
            WHEN 3 THEN N'Inventory Management'
            WHEN 4 THEN N'Manufacturing'
            ELSE N'** Invalid **'
        END;

    RETURN @ret
END;

The following command will return the department ” based on the supplied input parameter.

SELECT DBO.[ufnGetDepartmentName](1) AS DepartmentName

Output : Research and Development

2. Inline Table-Valued user defined Function

An inline table valued function will return the table which is derived from single select statement. There is no need to specify table variable name or column names as it is directly derived from select statement.  Duplicate column are not allowed. It can be a best alternative to the view in sql.

CREATE FUNCTION [ufnGetEmployeeDetails]
(
	@EmployeeId INT
)
RETURNS TABLE
AS
RETURN
(
	SELECT EMPLOYEEID,FIRSTNAME,LASTNAME,ADDRESS,CITY,STATE FROM EMPLOYEE
	WHERE EMPLOYEEID = @EMPLOYEEID
)
GO

3. Multi-statement table valued user defined function

Multi statement table valued function will return a table which can be a out come of one or more sql statements. Unlike inline table valued function here we must have to define the table structure that is being returned.

CREATE FUNCTION FNGETEMPLOYEEDETAILS
(
	@EMPLOYEEID INT
)
RETURNS @EMPLOYEE TABLE
(
	EMPLOYEEID INT NOT NULL,
	NAME VARCHAR(200) NOT NULL,
	ADDRESS VARCHAR(200),
	CITY VARCHAR(50),
	STATE VARCHAR(50),
	DEPARTMENT VARCHAR(50),
	SALARY DECIMAL(18,3),
	PANNUMBER VARCHAR(20)

)
AS
BEGIN
	INSERT INTO @EMPLOYEE
	SELECT E.EMPLOYEEID,E.FIRSTNAME + ' ' + E.LASTNAME AS NAME,E.ADDRESS,
	E.CITY,E.STATE ,ED.DEPARTMENT,ED.SALARY,ED.PANNUMBER
	FROM EMPLOYEE E
	INNER JOIN EMPLOYEEDETAILS ED ON ED.EMPLOYEEID = E.EMPLOYEEID
	WHERE E.EMPLOYEEID = @EMPLOYEEID
	RETURN
END
GO

Execute the above function as per below select statement.

SELECT * FROM FNGETEMPLOYEEDETAILS(1)