SQL Functions

Let's say that in SQL you want to add values in a single column for all rows returned in your query.  Maybe you want to count the number of rows that are returned from your query.  Or maybe you want to find the average of the values for all rows using the same column.  It is possible to do every one of these situations using something called SQL Functions.

What is a SQL Function? A function is a routine that takes the parameters you provide, performs a certain set of actions, and then returns the results from those actions.  For example, using the SUM(), COUNT() and AVG() functions, you can accomplish the situations above.

Now, there are two types of functions in SQL Server.  They are as follows:

• Built-in functions
• Created by SQL Server
• User defined functions
• Created by the user

Built-in functions include those mentioned above, as well as many others, such as

• UPPER()
• Shows all letters in uppercase
• LOWER()
• Shows all letters in lowercase
• MAX()
• Maximum value in a column
• MIN()
• Minimum value in a column
• And many more

A user can also create a function for his or her needs.  The syntax looks like this:

--Transact-SQL Scalar Function Syntax
CREATE FUNCTION [ schema_name. ] function_name 
( [ { @parameter_name [ AS ][ type_schema_name. ] parameter_data_type 
    [ = default ] [ READONLY ] } 
    [ ,...n ]
  ]
)
RETURNS return_data_type
    [ WITH  [ ,...n ] ]
    [ AS ]
    BEGIN 
        function_body 
        RETURN scalar_expression
    END
[ ; ]

For example, let's say that I need a function that calculates the square footage.  My function would look like this:

CREATE FUNCTION sqFeet
(@SideOne decimal(4,1), @SideTwo decimal(4,1))

RETURNS decimal(14,3)

AS

BEGIN

RETURN (@SideOne * @SideTwo)

END

To call your function, you would then run the following query:

SELECT sqFeet(2, 2)

Which would return the result: 4

Now you might be asking: what are the benefits of using SQL Functions instead of using something else, like SQL Stored Procedures? Well, SQL Functions allow you to return results inline, instead of having to execute a stored procedure and returning the results as an output parameter.  Also, a SQL Function can be used at different parts within a T-SQL statement, whether in the SELECT statement, WHERE or HAVING clauses.

Where can you see using a SQL Function in your organization?