Functions in SQL Server

In SQL, the set of statements are stored permanently in a function. The functions are user defined functions.

• User defined functions
• Scalar functions
• Table valued functions

User defined functions

The user defined functions is the object of database containing statements used for used for performing the process, accepting the parameters, provide the result as the output. The value returned after the function is processed can be scalar for tabled value.

The functions are created only if the user needs to perform the actions on particular table. The coding is implemented only if the user does not need any fixed changes to the objects outside the functions. The database table cannot be altered from the function.

The different parts of the functions are as listed below:

1. The name of the function
2. The name of the input parameter and the data type
3. The options applied to the input parameter
4. The return parameter data type
5. More than one T – SQL statements

The CREATE FUNCTION is used for creation of a function in SQL. The syntax is:

Code:

CREATE FUNCTION [ schemaname. ] [ functionname  ( [ { parametername [ AS ] 
[ type_schemaname. ] parameterdatatype [ = default ] } [ ,….n ]
)
RETURNS return datatype [ WITH [ SCHEMABINDING ] [ ENCRYPTION ] 
[ EXECUTEAS ] [ ,…n ] ]
[ AS ] 
BEGIN 
    Body of the function
    Return expression
END
[ ; ]

Where,

• schemaname is the name of the schema for the user defined function
• functionname is the name of the function
• parametername is the parameter used in the user defined function
• [type_schema_name. ] parameter_datatype is the data type used in the parameter
• [ = default ] is the default value of the parameter
• return_data_type is the return value of the user defined function

Scalar functions

A single parameter is accepted by the scalar function and a single data value is returned. The RETURNS clause is used for returning the result. The data type returned by the function cannot be text, image, cursor, ntext, and timestamp. The group of statements are added inside the BEGIN….END block.

Example for demonstrating the use of scalar function

The total marks of the student are added and the percentage value for the corresponding student is determined. Create a scalar function for the same.

Code:

create function percentage1 ( @totalmarks int )
returns int
as
    begin
        return ( @totalmarks * 100 / 100 )
    end

Execute the function using the following statement.

Code:

declare @totalmarks int
set @totalmarks = percentage1 ( 870 )
print @totalmarks

Table valued functions

The table values function always provides table as an output which can be accessed from the SELECT statement. The set of rows are stored in the table data. There are two types of table valued functions as:

1. Inline table valued function
2. Multistatement table valued function

Inline table valued function

The inline table valued function always provides a variable of the data type as table. A SELECT statement is used for calculating the data. There is no body for a function within begin and end blocks.

Example:

Consider an inline table valued function as location_name which has @locname as the input parameter and provides the all the corresponding details from the table.

Code:

create function location_name ( @locname varchar(40) )
returns table
as
return ( select * from orderdata where location = @locname )
go

Execute the following query to produce the output from the function.

Code:

select * from location_name ( ‘Melbourne’ )

Multistatement table valued function

The multistatement table valued function contains set of statements used for creating a table which is returned by the calling statement. The BEGIN and END blocks are present in the function declaration. The temporary table is used for returning the result set and is dependent on the function.

Consider the following example to demonstrate the use of multistatement table valued function.

Code:

create function productdetails ( @ProductID int )
returns @table table
    ( ProductID int not null,
    productname varchar(20) not null,
    quantity int not null,
    location varchar(20) not null,
    price int not null)
    as
    begin
        insert @table
        select * from productdata where ProductID = @ProductID
        return
    end

Execute the following query to display the value.

Code:

select * from productdetails ( 411 )