SQL Server Functions
Function is set of statements or set of queries that take input parameter, process it and return the result. Function must return a value that can be a single or table. we can use sql function with DML command like insert , delete, update.
Types of function
1:- System Defined Function
2:- User Defined Function
System Defined
Function
There is two type of system defined function that is defined
by Sql Server for different purpose. We can use these function as per our requirements.
1:- Scalar Function
Scalar functions works on a single value and returns a
single value. Following are the most useful scalar function in SQL server and
it returns a single value
- UPPER () - Converts a input string to upper case
- LOWER () - Converts a input string to lower case
- SUBSTRING () - Extract characters from a text field
- LEN () - Returns the length of a input value
- ROUND () - Rounds a numeric value to the number of decimals specified
- GETDATE () - Returns the current system date and time
- FORMAT() - Formats how a field is to be displayed
For more detail about Sql scalar function, click here
2:- Aggregate
Function
Aggregate functions work on a collection of values and
return a single value. Following are the most used Sql aggregate functions.
- MAX() - Returns the largest value of the selected column
- MIN() - Returns the smallest value of the selected column.
- AVG() - Returns the average value of a numeric column.
- SUM() - Returns the total sum of a numeric column.
- COUNT() - Returns the number of rows that matches a specified criteria.
For more detail about Sql Aggregate function, click here
User Defined Function
User defined function is created by user in database as per their
requirements. There are 3 types of user defined functions.
- · User Defined Scalar Function
- · Inline Table-Valued Function
- · Multi-Statement Table-Valued Function
1:- User Defined Scalar Function
This function also returns single value as a result. We
return any data type value from user defined scalar function.
Example:-
Create FUNCTION [dbo].[enumAbsentReason] ( @EnumId INT ) RETURNS VARCHAR(100) AS BEGIN -- Declare the return variable here DECLARE @result VARCHAR(100) SET @result = (SELECT CASE WHEN @EnumId = 1 THEN 'Hospitalization' WHEN @EnumId = 2 THEN 'Contractual Absence' WHEN @EnumId = 3 THEN 'Absent' WHEN @EnumId = 4 THEN 'Other' WHEN @EnumId = 5 THEN 'AWOL' WHEN @EnumId = 6 THEN 'Detention' WHEN @EnumId = 7 THEN 'Home Visit' ELSE '' END) RETURN @result END
Calling above
Function
DECLARE @EnumId int=1;
Select dbo.enumAbsentReason(@EnumId)
Output:- Hospitalization
2:- Inline
Table-Valued Function
Inline Table-Valued Function returns the table value as
result after performing certain process. it is stored in Inline Table-Valued
Function in database. it should be done by using SELECT statements
Example:-
CREATE FUNCTION GetEmployeelist() RETURNS TABLE AS RETURN ( -- Add the SELECT statement with parameter references here SELECT * from Employee where Department= ) GO
Calling Inline
Table-Valued Function
SELECT * FROM dbo.GetEmployeelist()
Output:-
Id Name Age Address Department Gender 1 Alok Kumar Singh 30 IN IT Male 2 Tomas Paul 45 NZ IT Male 3 Cristomfer Dee 85 AU HR Female 4 Niel Macengi 55 US BPO Female 5 Rahul Singh 25 IN IT Male 6 Manoj Singh 12 IN IT Male 7 Adam Zempa 58 IN BPO Male 8 Crisno Crief 96 AU HR Female 9 David Martin 56 AU IT Male 10 Ricky Ponting 21 US BPO Male 11 Poing Cahndda 66 IN IT Male 12 Chandan Subhash 46 IN HR Male 13 M. Shekh 45 NZ BPO Female
3:-Multi-Statement
Table-Valued Function
This function returns the table value as result after performing
certain process. In Multi-Statement
Table-Valued Function, you are allowed to use table variables as per your requirement and
then this table variables can return as result set.
Example:-
Create FUNCTION [dbo].[GetEmployeebyDepartment] ( @Department varchar(50)=null ) RETURNS @tblEmployeeInfo TABLE ( EmployeeId INT, Name VARCHAR(255), Department varchar(50), Address varchar(50) ) AS BEGIN INSERT INTO @tblEmployeeInfo SELECT id,Name,Department,Address from Employee where Department=@Department RETURN END
Calling Inline
Table-Valued Function
SELECT * FROM GetEmployeebyDepartment('IT')
Output:-
EmployeeId Name Department Address 1 Alok Kumar Singh IT IN 2 Tomas Paul IT NZ 5 Rahul Singh IT IN 6 Manoj Singh IT IN 9 David Martin IT AU 11 Poing Cahndda IT IN
Limitation of User Defined Function
- Can not Error Handling: RAISERROR statement cannot be used within a User Definded Function. You can not even check the value of the @@ERROR global variable within a function
- Cannot Use Temporary Tables: User Definded Function can not make use of temporary tables. You are allowed to use table variables within a User Definded Function.
- Cannot Execute Dynamic SQL: User Definded Function also cannot execute dynamically SQL statements. If you want to create a statement dynamically based on the parameter values, you should use stored procedures.
- Cannot Call Stored Procedures: Stored procedures can not be call within User Definded Function.
- Returns Only One Result Set: User Definded Function can return only one rowset to the user, whereas stored procedures can return multiple rowsets
- Limitation of Using Cursors: User Definded Function cannot return data to the user through the FETCH keyword within a cursor.
- Smaller Number of Parameters:- User Definded Function can have up to 1024 parameters, whereas stored procedures support up to 2100 parameters
- No Use of Non-deterministic Built-in Functions: User Definded Function cannot use non-deterministic built-in functions. This rule prohibits usage of very common functions such as GETDATE() or RAND() in UDFs.
nice article. keep it up bro
ReplyDelete