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: The name of the function The name of the input parameter and the data type The options applied to the input parameter The return parameter data type 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: Inline table valued function 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 )