Function
Functions are a very important concept in SQL Server databases. In T-SQL a function is considered an object. Functions must have a name but the function name can never start with a special character such as @, $, #, and so on.
A function return must a result. So that is also called a function that returns a result or a value. When we create it a function must specify a value type that will return a value.
The following is some Important information about functions:
- Functions only work with select statements.
- Functions can be used anywhere in SQL, like AVG, COUNT, SUM, MIN, DATE and so on with select statements.
- Functions compile every time.
- Functions must return a value or result.
- Functions only work with input parameters.
- Try and catch statements are not used in functions.
Function Types
The following is the function list in SQL Server databases.
![SQL Server database]()
This article is providing some useful function information one-by-one. First, create a table in your database with some records as in the following:
![Create One Table]()
Basically two types of functions are available in the database:
- User Defined function: User defined functions are create by the user.
- System Defined Function: System functions are builtin database functions.
User Defined Functions
- Table Valued Function.
- Scalar Valued Function.
Table Valued Functions
In this type of function we select table data using a user created function.
- Create function Fun_EmployeeInformation()
- returns table
- as
- return(select * from Employee )
![Table Valued Function]()
Now see a created function in the database.
![see crated function in databas]()
Now getting Employee table information with the created function
Fun_EmployeeInformation() using a select statement.
Scalar function
Now we are getting an Employee table with two different data joined and displayed in a single column data row. Here create a two-column join function as in the following:
- create function fun_JoinEmpColumnInfo
- (
- @EmpContact nchar(15),
- @EmpEmail nvarchar(50),
- @EmpCity varchar(30)
- )
- returns nvarchar(100)
- as
- begin return(select @EmpContact+ ' ' +@EmpEmail + ' ' + @EmpCity)
- end
![Scalar function]()
Now see a create scalar function in the database.
![database]()
Now the created scalar function is used for displaying Employee info in one column data row as in the following:
System function
This function is used for inserting records and is a system built-in function.
![System function]()
Here provide some Aggregate basic function example with our Employee Table.
This function operates on employee salary records.
Getting the highest salary record with the
max() function as in the following.
Command
- select max(salary) as Salary from employee
![select max]()
Getting the lowest salary record with
the
min() function as in the following.
Command
- select min(salary) as Salary from employee
![select min]()
Count the total salary with the
sum() function as in the following.
Command
- select sum(salary) as Salary from employee
![select sum]()
We are showing a basic example of how to use a system function. Many more functions are available in the database.
Now we will show one more example of how to store data using a function and display that stored data using a SQL print command.
- create function fun_PrintNumber()
- returns decimal(7,2)
- as
- begin
- return 1000.13
- end
![create function]()
Now call a function and display the stored record using the print command.
- print dbo.fun_PrintNumber()
![call a function]()
Now one more mathematical function to create a two-number addition.
- CREATE FUNCTION Fun_Addition(@Num1 Decimal(7,2),
- @Num2 Decimal(7,2))
- RETURNS Decimal(7,2)
- Begin
- DECLARE @Result Decimal(7,2)
- SET @Result = @Num1 + @Num2
- RETURN @Result
- end
![mathematical function]()
Now call an addition function as in the following:
- print dbo.Fun_Addition(12,13)
![addition function]()
I hope you understand about functions and how they work in SQL Server databases.