SQL Server Functions- An Introduction
Function in a database can be defined as the code segment consisting of a logical group of SQL statements which takes some input arguments (if required), run in a sequential order and returns the output either in the form of a single value or in the form of a table.Through this article, I am trying to give the overview of the System defined functions,User defined functions, their advantages, their disadvantages and their differences with the Stored procedures.
Types of Functions: –
In SQL, Functions can be categorized into two categories:-
- System Defined Function
- User Defined Function (UDF)
System defined function can again be further divided into further subsections which are given below:-
1. Aggregate function.
Example: – Avg (), Min (), Max ()
2. Configuration function.
Example:-@@servername (), @@version()
3. Cursor function.
Example: -@@Fetch_status
4. Date and Time function.
Example: – Getdate (), Month (), Day (), Year ()
5. Mathematical function.
Example: – Floor (), Abs ()
6. Metadata function.
Example: – Db_Name (), File_Name ()
7. Other functions.
Example: – cast (), convert ()
8. Rowset function.
Example: – Openxml (), Openrowset ()
9. Security function.
Example:-user(), User_Id (), User_Name ()
10. String function.
Example: – Char (), Left (), Len ()
11. System Statistical function.
Example:-@@connections
12. Text and Image function
Example: – Textvalid ()
Types of UDF:-
Similarly, UDF can be divided Into 3 categories:-
- Scalar UDF
- Inline Table UDF
- Multi statements UDF
Scalar UDF:-
The UDFs which only returns only single values comes into this category.
Syntax for creating Scalar UDFs:-
CREATE FUNCTION (FUNCTION name)
(
(Input Variable name) (data type)
)
Returns (returning variable data type)
AS
BEGIN
(FUNCTION body)
Return (returning variable name)
End
Example
CREATE FUNCTION fn_getempname
(
@empid Int
)
RETURNS nvarchar(100)
AS
BEGIN
DECLARE @empname AS Nvarchar(100)
SELECT @empname=empfirstname + ‘ ‘ + emplAStname
FROM employee
WHERE empid=@empid
RETURN @empname
END
Syntax for executing the Scalar UDF
SELECT dbo. (FUNCTION name) (input variable name)
For example, to execute the above UDF we use the following syntax:-
SELECT DBO.[fn_getempname](1) /* Here 1 is the empid*/
or
SELECT DBO.[fn_getempname](1) AS EmployeeName/*To get the output under the column EmployeeName */
If we want to store the value of this FUNCTION in an variable the we have to use the following syntax:-
DECLARE @name AS nvarchar(100)
SELECT @name =dbo.[fn_getempname](1)
SELECT @name
Here we first needs to define a variable (@name) which will be going to store the value return by the function and then used this variable in the SELECT statement to store the value.
Result
EmployeeName
Samir Singh
Explanation
This function will return the name of the employee whose empid we passed in the function as the Input parameter.
Inline Table UDF:-
The UDF which contains a single inline SQL statement and returns the data in the form of table is called Inline Table UDF.
Syntax for creating Inline Table UDF:-
CREATE FUNCTION (FUNCTION name)
(
(Input Variable name) (data type)
)
Returns Table
AS
Return (SELECT statement)
Example:-
Create Function fn_getempinfo
(
@empid Int
)
Returns Table
As
Return Select empid,empnumber,empfirstname,emplastname,empemail
From employee
Where empid=@empid
Syntax for executing the Inline Table UDFs
Select (columns names) from dbo. (Function name) (Input Parameter)
SELECT empid,empnumber,empfirstname,emplastname,empemail
FROM dbo.[fn_getempinfo](1)
Result
Empid Empnumber Empfirstname Emplastname Empemail
1 A001 Samir Singh samir@abc.com
Explanation
This FUNCTION will return the columns empid, empnumber, empfirstname, emplAStname, empemail of the employee AS a table variable whose employeeid is given AS the input parameter to the FUNCTION.
Multi statements UDF: –
The UDFs which contain multiple SQL statements to returns the data in the form of table is called Multi Statements UDFs.
Syntax for creating Multi Statements UDFs: –
Create Function
(
(Input Variable name) (data type)
)
Returns (table variable) Table (table columns)
As
Begin
(Function body)
Return
End
Example
CREATE FUNCTION fn_GetEmpdepartmentinfo
(
@empid Int
)
Returns @Empproject Table
(
Employeename Nvarchar(100),Empemail Nvarchar(50),Departmentname Nvarchar(100)
)
AS
BEGIN
Insert Into @Empproject(Employeename,Empemail,Departmentname)
SELECT empfirstname + ‘ ‘+ emplAStname ,empemail,departmentname
FROM employee Inner Join EmpdepartmentInfo On employee.empid=EmpdepartmentInfo.empid
Inner join Department On EmpdepartmentInfo.departmentid=Department.departmentid
WHERE employee.empid=@empid
RETURN
END
Syntax for executing the Multi Statements UDF
Select (columns names) from dbo. (Function name) (Input Parameter)
SELECT Employeename,Empemail,Departmentname
FROM dbo.[fn_GetEmpdepartmentinfo](1)
Result
Employeename Empemail Departmentname
Samir Singh samir@abc.com Accounts
Explanation
This function will returns the Employeename,Empemail,Departmentname of the employee whose empid we pass as the input parameter to the function.
- A stored procedure can return a value or it may not return any value but in case of function, a function has to return a value.
- Stored procedure in SQL Server cannot we executed within the DML statement. It has to be executed with the help of EXEC or EXECUTE keyword but a function can be executed within the DML statement.
- A function can be called from within the Stored Procedure but a stored procedure cannot be called from within a function.
- We can use result set return by the function as a table in Join statements but we can’t use ResultSet return from stored procedure as table in Join statements.
- Transaction management is not possible in function but it is possible in Stored procedures.
- Print function cannot be called within the function but it can be called within the stored procedure.
Advantages of UDF:-
1. SQL Functions can be used in a DML statement. It means we can use execute the FUNCTION within the SELECT statement.
2. We can use the recursive FUNCTION to get the hierarchical information.
For example, if we want to get the all the employee which are directly or indirectly have the manager whose empid is given AS the input parameter(@managerid), then we can use the following FUNCTION which calls itself.
CREATE FUNCTION fn_recuursivesample
(
@managerid int,
@mode int
)
Returns @temporder table(employeeid int , managerid int)
AS
BEGIN
DECLARE @count AS int
DECLARE @empid AS int
DECLARE @next_empid AS int
DECLARE @next_orderid AS int
IF @mode=0
BEGIN
INSERT Into @temporder
SELECT @managerid,(SELECT managerid FROM employee WHERE empid=@managerid)
END
SELECT @count=count(empid)FROM employee WHERE managerid=@managerid
IF @count=1
BEGIN
SELECT @empid=empid FROM employee WHERE managerid=@managerid
INSERT Into @temporder Values(@empid,@managerid)
INSERT Into @temporder SELECT * FROM dbo.fn_recuursivesample(@empid,1)
END
ELSE IF @count>1
BEGIN
SELECT @empid=min(empid)FROM employee WHERE managerid=@managerid
WHILE @empid>0
BEGIN
Insert Into @temporder Values(@empid,@managerid)
Insert Into @temporder
SELECT * FROM dbo.fn_recuursivesample(@empid,1)
SELECT @next_empid=isnull(min(empid),0)
FROM employee
WHERE empid >@empid and managerid=@managerid
SET @empid=@next_empid
END
END
RETURN
END
Syntax to execute the above function
SELECT employeeid, managerid FROM dbo. fn_recuursivesample(1,0)
Result:-
Employeeid Managerid
1 0
2 1
5 2
6 5
8 6
7 5
3 1
4 4
3. We can use the Function in the Join queries.
4. We can used UDFs as the parametrized view(a view which take input parameters).
5. UDFs also reduce the compilation cost of SQL codes by caching the plans and reusing them for repeated executions which mean it does not need to be re-parsed and recompiled with each use and it result in better execution time.
6. We can use the WHERE clause to filter the rows as per our needs FROM the result set return by the Function. For example, in the above function fn_recuursivesample, if we want to get the empid of only those employee which are directly under the employee whose empid is given AS the input parameter, the we can use the “WHERE” clause to filter the dataset return by the function.
SELECT employeeid, managerid FROM dbo. fn_recuursivesample(1,0) WHERE managerid=1
It will give the following result:-
Employeeid Managerid
2 1
3 1
4 1
7. UDFs can be used to divide the complex code into shorter and simple blocks of code which helps in maintenance of the code.
Disadvantages of the UDF
- We cannot use temporary tables inside the UDF.
- We cannot use Print command inside the UDF.
- We can’t use the Insert, update and delete command inside the function. We can only use these commands on the table variable defined inside the function.
- We cannot use the try catch statement inside the function for the debugging purpose which makes it difficult to debug.
- We can’t use transaction inside the function.
- We can’t call the stored procedure from inside the function.
- We can’t return multiple record sets from a function as we can do in case of Stored Procedure.
- We can’t use Getdate() function within a UDF.
Conclusions
Functions are the feature which is given by the SQL Server to make our complex code shorter and less complex. We can either use the Functions which are already provided by the SQL Server known as the System defined Function such as Avg (), Min (), Max () or we can write our own UDF. If we want to get only a scalar value as result, we can create Scalar UDFs or if we want to get a ResultSet, then we can create Inline Table UDFs or Multi statements UDFs. We can also used Functions in the SELECT statement and in join statements. But while creating the UDFs we should also keeps in our mind that no DML(Insert, Update, Delete) statement can be performed inside the UDF. Also we can’t use temporary table inside the Function. Also we can’t use transaction management inside the UDF.
Discover more from Technology with Vivek Johari
Subscribe to get the latest posts sent to your email.