Sunny Books
What we have

SQL Server user defined functions

In SQL Server there are three types of USER Defined Functions:
1. Scalar functions
2. Inline table-valued functions
3. Multi-statement table-valued functions

Scaler functions

Scaler functions may or may not have parameters, but always return a single (scalar) value. The returned value can be of any data types, except text, ntext, image, cursor, and timestamp.

Syntax

CREATE FUNCTION function_name ((@paramenter1 Datatype, @parameter2 Datatype,...,
	parameterN Datatype)
RETURNS return_datatype
AS
BEGIN
	//Function body
	RETURN return_datatype
END

When calling a scalar user-defined function, we need to supply a two-part name: ownerName.FunctionName. We can also invoke it using the complete three part name: DatabaseName.Ownername.FunctionName.

Scalar user-defined functions can be used in SELECT and WHERE clauses.

Example: get age function

CREATE FUNCTION getAge (@dob DATE)
RETURNS INT
AS
BEGIN
	DECLARE @age INT
	SET @age = DATEDIFF(YEAR,@dob, GETDATE()) - 
		CASE
			WHEN ( MONTH(@dob) > MONTH(GETDATE()) OR 
			( MONTH(@dob)=MONTH(GETDATE()) AND DAY(@dob) > DAY(GETDATE())   ) )
			THEN 1
			ELSE 0
		END
	RETURN @age
END

Example: using function getAge

SELECT birthday, dbo.getAge(birthday) as age
FROM [em].[employee]
WHERE dbo.getAge(birthday)>30

The result:

Birthday Age
1978-09-10 16:34:12.00034
1980-12-20 09:25:07.00032
1979-09-24 12:36:43.00033
1980-01-23 06:25:43.00033
1978-05-25 16:08:27.00034

Inline table valued functions

Scalar function returns a scalar value while Inline table valued function returns a table.
The function body is not enclosed between BEGIN and END block.
The structure of the table returned is determined by the SELECT statement in the function.
Inline table valued function can be used to achieve the functionality of parameterized views.
The table returned by inline table valued function can also be used in JOINS with other tables.

Example

CREATE FUNCTION employee_high_salary (@salary int)
RETURNS TABLE
AS 
RETURN (SELECT [employee_id], [name], [salary], [department_id]
        FROM [Employees].[em].[employee]
		WHERE salary>=@salary
)

//To call the function
SELECT * FROM employee_high_salary('7500')

The result

Employment id Name Salary Department id
3Rose75002
4Jack76003
5Tom80001
7Chantal76001
9Kaven8000NULL
10Erik7800NULL

// another example
SELECT * FROM employee_high_salary('7500') s
LEFT JOIN [Employees].[em].[department] d ON s.department_id = d.department_id 

The result

Employment id Name Salary Department id Department id Department name Department location
3Rose750022PayrollNew York
4Jack760033HRBoston
5Tom800011ITChichago
7Chantal760011ITChichago
9Kaven8000NULLNULLNULLNULL
10Erik7800NULLNULLNULLNULL

Multi-statement table value functions

Example

CREATE FUNCTION fn_msyvf_employee_high_salary (@salary int)
RETURNS @table TABLE (employee_id int, name nvarchar(20), salary int, department_id int)
AS
BEGIN
	INSERT INTO @table
	SELECT [employee_id], [name], [salary], [department_id]
    FROM [Employees].[em].[employee]
	WHERE salary>=@salary
	RETURN
END

//Call the function
SELECT * FROM fn_msyvf_employee_high_salary('7500')

Get the same result as above function.

Difference among these user defined functions

1. In an inline table valued function, the RETURN clause cannot contain the structure of the table the function returns, while with the multi-statement table valued function we specify the structure of the table that gets returned.

2. Inline table valued function cannot have BEGIN and END block, while multi-statement function can have.

3. Inline table valued functions are better for performance than multi-statement table valued functions. The reason is SQL Server treats an inline table valued function much like it would a view and treats a multi-statement table valued function similar to how it would a stored procedure.

4. It's possible to update the underlying table using an inline table valued function, but not possible using multi-statement table valued function.

SUNWEB EXPERT