What is a function
in SQL Server?
A SQL function is a set of statements that you can pass input values, perform an action and return the result and the result can be single value or a table value.
A SQL function is a set of statements that you can pass input values, perform an action and return the result and the result can be single value or a table value.
When
do you use SQL function?
When I am writing an expression and I want to
return some value in the SELECT statement from this expression that time I can
use a function.
Types of SQL functions,
1) System defined functions
· Scalar Functions: - abs, round, upper, lower, trim and convert etc.
· Aggregate Functions: - min, max, avg and count etc.
2) User defined functions
The user defined functions are created in the user defined database. This user defined function can be “inline table valued function”, “scalar value function” or “multi statement table valued function”.
Syntax:-
CREATE FUNCTION <Scalar_Function_Name, sysname, FunctionName> ( -- Add the parameters for the function here <@Param1, sysname, @p1> <Data_Type_For_Param1, , int> ) RETURNS <Function_Data_Type, ,int> AS BEGIN -- Declare the return variable here -- Add the T-SQL statements to compute the return value here -- Return the result of the function END GO OR CREATE FUNCTION <Inline_Function_Name, sysname, FunctionName> ( -- Add the parameters for the function here <@param1, sysname, @p1> <Data_Type_For_Param1, , int>, <@param2, sysname, @p2> <Data_Type_For_Param2, , char> ) RETURNS TABLE AS RETURN ( -- Add the SELECT statement with parameter references here SELECT 0 ) GO
Examples,
The
SQL function Summary:-
1. SQL
function must be be return a value or a table value.
2. SQL
function accepts only input parameters.
3. SQL
functions can’t use in insert, update and delete in the database tables.
4. SQL
functions can be nested up to 32 levels.
5. The
user defined SQL function can have up to 1023 input parameters.
6. The
user defined SQL function can't return XML data type and it is not support to exception
handling.
7. It
is only call in the stored procedures and it is not support to the set options
like ROWCOUNT etc.
I hope it is helpful
to you! Thank you!
|
Anil Singh is an author, tech blogger, and software programmer. Book writing, tech blogging is something do extra and Anil love doing it. For more detail, kindly refer to this link..
|