SQL Server Functions,Types and Examples

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.

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,

How to create SQL function without parameter?

How to create SQL function with parameter?

How to call a function in storeprocedure?

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!