SQL/NoSQL Public Diary: User-defined Function

User-defined Function

User defined functions allow modular programming. You can create the function once, store it in the database, and call it any number of times in your program.
Similar to stored procedures, Transact-SQL user-defined functions reduce the compilation cost of Transact-SQL code by caching the plans and reusing them for repeated executions. This means the user-defined function does not need to be re-parsed and re-optimized with each use resulting in much faster execution times.
User-defined functions can be modified independently of the program source code.
They allow faster execution and can reduce network traffic.

Function Vs. Stored Procedures

Following are some major differences between a stored procedure and user defined

Functions:-

A) Function must return a value but in SP it is optional.

B) Function can have only input parameters for it whereas procedure can have input/output parameter.

C) Functions called by a SQL statement i.e. “SELECT” clause while SP’s are called by EXEC or EXECUTE.

D) DML statements are the only type of statement you can run within a function i.e. No DDL, DCL, or TCL statements in Function where as these are allowed in Store Procedure.

E) Functions can be called from procedure where procedure can’t be called from a function.

F) If there is an error in function its stops executing. But in SP’s it just ignores the Error and moves to the next statement.

Types of Functions

Scalar Function:

User-defined scalar functions return a single data value of the type defined in the RETURNS clause. For an inline scalar function, there is no function body; the scalar value is the result of a single statement. For a multistatement scalar function, the function body, defined in a BEGIN...END block, contains a series of Transact-SQL statements that return the single value. The return type can be any data type except text, ntext, image, cursor, and timestamp.

Table-Valued Functions:

User-defined table-valued functions return a table data type. For an inline table-valued function, there is no function body; the table is the result set of a single SELECT statement.

System Functions:

SQL Server provides many system functions that you can use to perform a variety of operations. They cannot be modified. For more information, see Built-in Functions (Transact-SQL), System Stored Functions (Transact-SQL), and Dynamic Management Views and Functions (Transact-SQL).

Creating and implementing Scalar Functions:

You can create a scalar function using CREATE FUNCTION statement. You can call upon a scalar function using SELECT statement.

How to use Function with Queries:

We have [Shopping List]table in our database. Which has the prices of different products. Now if we want to use our function in this table then we will achieve this as follows:

First let see the contents of the table [Shopping List].

Now we are going to alter our previously created function because the price list data is not in integer so the result will not be accurate. Alter the function as follows:

Now let’s use our function in a query and you will see the result as follows:

SELECT [description],price,dbo.returndouble(Price)AS DoublePrice FROM dbo.[Shopping List]

Functions with multiple parameters:

Functions can accept one or more input parameters and return a scalar or a table like result set.

Now we are going to add two numbers which will take two number as input and then returns the sum of them.

Code:

USE DBTest

CREATE FUNCTION Add_Two_Numbers(@num1 INT, @num2 INT)

RETURNS INT

AS BEGIN

DECLARE @Total INT

SELECT @Total=@num1+@num2

RETURN @Total

END

Now lets call this function as follows:

Now we can see the functions which we have created are under scaler-valued Function in object explorer as follows;

Note:

You must have to supply the schema name(in our case it was dbo) because SQL Server assumes that any function called by its simple name is system-supplied function i.e. Bulit-in function.

If you will call it without schema name you will get such a beautiful error as follows:

Let’s discuss second type of fucntion that is Table_valued functions.

Table-valued functions:

When functions are executed, they can return results in the form of a value or a table. Table function specify TABLE as the return type (RETURNS TABLE).

Table-valued functions look a lot like views because they both show us a tabular result set. Table functions have a great advantage over views: they allow a parameterized look at your table data.

Craeting and Implementing Table_Valued Functions:

The body of table-valued function will essentially conyain a query. Let’s begin with a query containing three fields all of the records from Employee Table.

This will become the main part of table-valued function. By placing the query within a set of parentheses, and after the keyword RETURN, we have the body of te function. The RETURNS TABLE keywords specifies that the table-valued function GetDetail must return the result in form of table as follows: