SQL SERVER – Functions

• A Function is also Stored Block of code similar to a Procedure.
• A Function is a Block of Code which will return only a single value.
• A Function is not a stand alone executable like a Procedure it can be executed in some other context also.
• A Function can be used in a Select Statement.
• Modifications to database tables, operations on cursors that are not local to the function are examples of actions that cannot be performed in a function.
• Try and Catch Statements cannot be used in the Functions.
• A user-defined function takes zero or more input parameters and returns either a scalar value or a table; a function can have a maximum of 1024 input parameters.
• User-defined functions do not support output parameters.
• When a parameter of the function has a default value, the keyword DEFAULT must be specified when calling the function to get the default value.

Functions are of 3 types:

1. Scalar Functions

2. Inline Table-valued Functions

3. Multistatement Table-valued Functions

Scalar Functions: Functions are scalar-valued if the RETURNS clause specifies one of the scalar data types.

Syntax: CREATE FUNCTION <function_name>

( [ @parameter_name [ AS ] data_type [ = default ] [ ,...n ] ] )

RETURNS data_type

[ WITH <function_option> [ ,...n ] ]

[ AS ]

BEGIN

function_body

RETURN scalar_expression

END

function_options can be any of these two:

1. Encryption
2. Schemabinding

ENCRYPTION: Indicates that the Database Engine encrypts the catalog view columns that contain the text of the CREATE FUNCTION statement.

SCHEMABINDING: Specifies that the function is bound to the database objects that it references. The binding of the function to the objects it references is removed only when one of the following actions occurs:

• The function is dropped.
• The function is modified by using the ALTER statement with the SCHEMABINDING option not specified.

-A Function that takes the Empno and Returns the total salary of the employee.

CREATE FUNCTION GET_TSAL (@EMPNO INT)

RETURNS MONEY

AS

BEGIN

DECLARE @TSAL MONEY

SELECT @TSAL=SAL + ISNULL (COMM, 0) FROM EMP WHERE EMPNO=@EMPNO

RETURN @TSAL

END

Syntax for Calling a Scalar Function:

SELECT <owner>.<function_name>( <list of values> )

Calling the above function:

SELECT DBO.GET_TSAL(1005)

Inline Table-valued Functions: These functions can return a table as an output. In inline table-valued functions, the TABLE return value is defined through a single SELECT statement. Inline functions do not have associated return variables.

Syntax: CREATE FUNCTION <function_name>

( [ @parameter_name [ AS ] data_type [ = default ] [ ,...n ] ] )

RETURNS TABLE

[ WITH <function_option> [ ,...n ] ]

[ AS ]

BEGIN

function_body

RETURN [ ( ] select_stmt [ ) ]

END

-A function which takes the deptno and returns the list of employees working in it by joining Emp and Dept tables.

CREATE FUNCTION GET_ED_DATA(@DEPTNO INT)

RETURNS TABLE

AS

RETURN (SELECT E.EMPNO, ENAME, E.SAL, D.DEPTNO, D.DNAME, D.LOC FROM EMP E INNER JOIN DEPT D ON E.DEPTNO=D.DEPTNO WHERE E.DEPTNO=@DEPTNO)

Syntax for Calling a Table Valued Functions:

SELECT < * | <collist> FROM <function_name>( <list of values> )

Calling the above function:

SELECT * FROM GET_ED_DATA(10)

SELECT EMPNO, ENAME, DEPTNO, DNAME FROM GET_ED_DATA(20)

Multistatement Table-valued Functions: These function are same as Inline Table-valued but the body of this functions can contain multiple statement in it and the structure of the table can be defined by us.

Syntax: CREATE FUNCTION <function_name>

( [ @parameter_name [ AS ] data_type [ = default ] [ ,...n ] ] )

RETURNS @return_variable TABLE < table_type_definition >

[ WITH <function_option> [ ,...n ] ]

[ AS ]

BEGIN

function_body

RETURN

END

-A function which, takes the Empno and calculates the Total Salary and Annual Salary of the employee and returns them.

CREATE FUNCTION GET_EMPDATA(@EMPNO INT)

RETURNS @MYTABLE TABLE(TOTSAL MONEY, ANNSAL MONEY)

AS

BEGIN

DECLARE @SAL MONEY, @COMM MONEY

DECLARE @TSAL MONEY, @ASAL MONEY

SELECT @SAL=SAL, @COMM=COMM FROM EMP WHERE EMPNO=@EMPNO

SET @TSAL=@SAL + ISNULL(@COMM, 0)

SET @ASAL=(@SAL + ISNULL(@COMM, 0)) * 12

INSERT INTO @MYTABLE VALUES(@TSAL, @ASAL)

RETURN

END

Calling the above function:

SELECT * FROM GET_ EMPDATA(1005)