Applying Batches, Stored Procedures and Functions Part – 6
Applying Functions
Alike to the stored procedure, an individual can form functions to save a group of T – SQL commands lastingly too. These functions are mentioned to as user – defined functions (UDFs) too. A User – Defined Function (UDF) is a database item which covers a group of T – SQL commands, receives factors, preforms an act, as well as gives back the result of that act as a data. The yield back data can either be a single scalar data or else a result group.
A User – Defined Function (UDF) have a restricted possibility as related to stored procedures. An individual can form functions in circumstances when an individual want to apply a program writing sensibleness which does not include a few lasting modifications to the database items separate from the function. For an instance, an individual cannot alter a database table or relation with the help of a function. A User – Defined Functions (UDFs) are of not the same sorts: Scalar Functions and Table – Valued Functions. For a database designer, it is significant to know how to form as well as handle diverse types of User – Defined Functions (UDFs).
Constructing User – Defined Functions (UDFs)
A User – Defined Function (UDF) covers the subsequent modules:
· The function name along with the noncompulsory schema and / or owner name.
· The input parameter name as well as their data type.
· The choices appropriate for the input parameter.
· The return parameter data type as well as the possible name.
· The choices appropriate for the return parameter.
· Lastly, one or several T – SQL command.
To form a function, an individual can castoff the CREATE FUNCTION command. The code of the CREATE FUNCTION command is:
CREATE FUNCTION [ My_Schema_Name ] . My_Function_Name
( [ { @ My_Parameter_Name [ AS ] [ Parameter_Type_Schema_Name ] . My_Parameter_Datatype [ = Default ] } ] )
RETURNS My_Return_Datatype
[ WITH < My_Function_Option > ]
[ AS ]
BEGIN
My_Function_Body
RETURN My_Expression
END [ ; ]
here,
· My_Schema_Name – It is name of the schema to which this User – Defined Function (UDF) will be belonging.
· My_Function_Name – It is the name of User – Defined Function (UDF) that an individual want to provide. Function names should obey with the guidelines for identifiers as well as should be exclusive within the database plus to its schema.
· @ My_Parameter_Name – It is a parameter in the User – Defined Function (UDF) one or several factors can be used.
· [ Parameter_Type_Schema_Name ] . My_Parameter_Datatype – It is the data type of the parameter declared, as well as optionally the schema to which it fits.
· Default – It is the default data for the parameter’s declared.
· My_Return_Datatype – It is the return data of a scalar User – Defined Function (UDF).
· My_Function_Body – It stipulates a sequence of T – SQL commands for completing a task.
A User – Defined Functions (UDFs) can be of dual sorts, Scalar plus Table – Valued functions. The meaning of every function is dissimilar. Consequently, it is significant to know in what manner to form every single type of function.
Forming Scalar Functions
The Scalar function receives a single factor plus gives back a single value of the data type indicated in the RETURNS part. A scalar function can give back whichever data type excluding image, timestamp, ntext, cursor, and ntext. A number of scalar functions, like current_timestamp, do not need some opinions. A function covers a group of T – SQL commands distinct in a BEGIN – END construct of the function body which gives back a single value.
Think through an instance of scalar function which calculates the monthly working hours of the employee taking the DailyWorkingHour as an input plus giving back a single data afterwards multiplying the data with the number of days:
CREATE FUNCTION Fn_MonthlyHours ( @ DailyHours INT )
RETURN INT
AS
BEGIN
RETURN (@DailyHours * 30 )
END
An individual can execute the above function by means of the subsequent commands:
DECLARE @ DailyHours INT
SET @ DailyHours = Fn_MonthlyHours ( 8 )
PRINT @ DailyHours
In the above syntax, @ DailyHours is a variable which will stock a data given back by the Fn_MonthlyHours function.
Forming Table – Valued Functions
A Table – Valued functions gives back a table or relation as an output that can be resultant as a portion of a SELECT command. Table – valued functions gives back the result as a table data type. The table data type is a different data type castoff to stock a group or rows or tuples that gives back the output set of a Table – Valued function. Table – Valued functions are of dual kinds:
· Inline Table – Valued function
· Multi – Command Table – Valued function
Inline Table – Valued Function
An Inline Table – Valued function gives back a variable of a table data type through the output set of a particular SELECT command. An inline function cannot cover a function body in the BEGIN and END commands.
Think through an instance where the Inline Table – Valued function, Fn_CustomerDetails takes a customer ID as factor as well as gives back the information of the customer from the [ Customer ] . [ Details ] table or relation. An individual can form the function by means of the subsequent command:
CREATE FUNCTION Fn_CustomerDetails (@ CustomerID NVARCHAR ( 10 ) )
RETURNS TABLE
AS
RETURN ( SELECT * FROM [ Customer ] . [ Details ] WHERE CustomerID =
@ CustomerID )
GO
An individual can practice the subsequent command to execute the Fn_CustomerDetails function with a definite parameter:
SELECT * FROM Fn_CustomerDetails ( ‘ C#25001 ’ )
Think through a different instance of an Inline Table – Valued function which takes ROI as a factor as well as gives back every data which have the ROI value less than the factor value:
CREATE FUNCTION Fn_ROI ( @ ROI INT )
RETURN TABLE
AS
RETURN ( SELECT * FROM [ Customer ] . [ Loan ] WHERE ROI < @ ROI )
GO
The previous function Fn_ROI will give back an output set which shows every data of the loan table where the ROI is less than the mentioned @ ROI in the factor.
Multi – Command Table – Valued Function
A Multi – Command Table – Valued function practices numerous commands to form the table or relation which is given back to the invoking command. The function body is comprised of the BEGIN and END construct that contains a group of T- SQL commands to form as well as to insert tows or tuples in a provisional table or relation. The provisional table or relation is given back in the output set in addition to that it is formed grounded on the requirement stated in the function body.
Think through an instance where the Multi – Command Table – Valued function, Fn_CustomerLoan is formed to give back a group of data from the [ Customer ] . [ Loan ] table or relation by means of using the subsequent commands:
CREATE FUNCTION Fn_CustomerDetails ( @ LoanType CHAR ( 25 ) )
RETURNS @ My_Table TABLE
( CustomerID VARCHAR ( 20 ) NOT NULL ,
LoanID VARCHAR ( 10 ) NOT NULL ,
LoanType CHAR ( 25 ) NOT NULL ,
ROI INT NOT NULL ,
LoanAmt MONEY NOT NULL ,
EMI MONEY NOT NULL ,
TimePeriod INT NOT NULL )
AS
BEGIN
INSERT @ My_Table SELECT * FROM [ Customer ] . [ Loan ] WHERE LoanType = @ LoanType
RETURN
END
The function gives back an output set in an arrangement of a provisional table or relation @ My_Table formed in the function. An individual can execute the function Fn_CustomerDetails by means of the subsequent command:
SELECT * FROM Fn_CustomerDetails ( ‘ Education ’ )
Based on the output set given by means of a function, a function can be classified as deterministic or else non – deterministic. Deterministic functions at all times gives the identical output on every occasion it is invoked through a precise group of input data. But, non – deterministic functions might give back not the same output every single time it is invoked through a precise group of input data.
For an instance consider the DATEADD function is the deterministic, that gives back the identical output for whichever given group of parameter data for its three (3) factors, on other hand the GETDATE is an instance of the non – deterministic function for the reason that it is at all times called no parameters, nevertheless the output data varies on every single implementation.
This is the last part of the article Applying Batches, Stored Procedures and Functions series.
Thank you