SQL Server - User Define Functions
Functions in SQL Server help us to perform specific tasks. It helps to increase the code re-usability.
Types of user define functions in SQL Server.
1) Scalar Function
2) Table-Valued Function
When to use the user define functions in SQL Server?
If we have requirement where some repetitive and complex code need to use more places and which is returning some type of
information that time we can create one common function of SQL Server and return required value.
Second important use of function is we can use SQL Server function with in-line select, insert and update statements of SQL Server.
Note : Important thing about SQL Server function is, it must return the value.
In Scalar Function it return single value where in Table-Valued function it return the table as value.
Let's start
1) Scalar (single value) Function.
Scalar means single value, In SQL Server Scalar function always return only one single value. This is most preferred function to use with select, insert and update statements.
Syntax of Scalar Function
If you observe the above function syntax
First create function with list of parameters if required .
Second and important is return statement. In second statement we declare return types function.
After that we use some statements in BEGIN - END block and return final value.
Practical example of Scalar Function.
Let's consider the below result set of student where we need to find out the percentage of each student with marks using scalar value function.
Example
Now function get created in Data Base Name -> Functions -> Scalar-Valued-Function folder -> function name that is GetPercentage. You can see in below image.
Example
Output
Note : Every user define scalar function always start with dbo.< function name >
2) Table-Valued Function.
This is second type of example of user define function. This function return table as value.
This is totally different from Scalar Value function because scalar function return single value where table value function return table as value.
Syntax of Scalar Function
Example
Note : From above example we return the table variable value in variable @StudentMarks. And with RETURN statement in table value function we not need to provide the table variable.
Now function get created in Data Base Name -> Functions -> Table-Valued-Function folder -> function name that is GetPercentage. You can see in below image.
Example
Output
Note : In above example we not use dbo.< function name > because this function it-self returning table as value.
How to drop user define function in SQL Server
DROP keyword we use to drop user define function in SQL Server.
Example
Output