Function in SQL

1. A function must have a name and a function name can never start with a special character such as @, $, #, and so on.
2. Functions only work with select statements.
3. Functions can be used anywhere in SQL, like AVG, COUNT, SUM, MIN, DATE and so on with select statements.
4. Functions compile every time.
5. Functions must return a value or result.
6. Functions only work with input parameters.
7. Try and catch statements are not used in functions.

SQL Server support two types of user defined functions:

1. Table Valued Functions
2. Scalar Valued Functions

Table Valued Functions

In this type of function, we select a table data using a user created function. 

Example:

1. Create function Fun_EmployeeInformation()      
2. returns table       
3. as      
4. return(select * from Employee  ) 

Syntax:

1. create function fun_JoinEmpColumnInfo  
2. (  
3.    @EmpContact nchar(15),  
4.    @EmpEmail nvarchar(50),  
5.    @EmpCity varchar(30)  
6. )  
7. returns nvarchar(100)  
8. as  
9. begin return(select @EmpContact+ ' ' +@EmpEmail + ' ' + @EmpCity)  
10. end