In this blog I am trying to explain how create a user define function using SQL Server 2008 R2.
In the SQL Server 2008 R2 have many predefined functions aggregate functions and Scalar functions which are worked for resolve our complex problems, and we are also known the method or functions resolve the complexity.
SQL aggregate functions return a single value, which is calculated from values in a column. Such as
1. AVG () - Returns the average value.
2. COUNT () - Returns the number of rows.
SQL scalar functions return a single value, based on the input value.
1. UCASE () - Converts a field to upper case.
2. LCASE () - Converts a field to lower case.
UDF (User Define Function):
UDF is the concept of User-Defined Functions that allow you to define your own T-SQL functions that can allow zero or more parameters and return a single scalar data value or a table data type.
We can create three types of UDF in SQL Server 2008 R2 and they are:
2. Inline Table-Valued
3. Multi-statement Table-valued.
Below I am giving an example for User Define Scalar Function which is single parameterizes function.
Above function will accept a value City name and return its Country name .
Now we will create a table:
Insert Values in table:
Use our function:
Above statement will make a virtual column Country’s name and whichCountry () method accepts the value of vCity column and whichCountry () method match the vCity columns values in its code such as if value is Banglore then whichCountry () method will return the India. After that below will show on your screen:
Below I’m using the UDF whichCountry () method during creating a table:
When we will insert values in the tblEmployeeFullInformation table we need only name and Employee Name and its city but country name will automatically get via whichCountry () method.
Insert Values in tblEmployeeFullInformation:
Inline table Value:
Its returns a table data type and is an extravagant alternative to a view as the user-defined function, it will take parameters into a T-SQL select command and in synopsis provide us with a parameterized, non-updateable view of the underlying tables.
Above code will create a table with EmployeeByCountry name. And when this table will execute with T-SQL select command then we also need to pass a parameter with the table name below is example:
Above code will create a virtual table which is shows the employee information as Employee Name and Employee City.