What is a SQL Function? A function in T-SQL is an object that allows us to perform specific tasks. For example, if someone wanted a cup of coffee, they could use the GetCoffee function. (Unfortunately, this is not an actual function on a SQL Server. But it will help us better understand the concept.)

The code to get the cup of coffee will have previously been written with the instructions on how to retrieve the coffee. However, each person would want their coffee prepared differently. So additional parameters or arguments are needed.

What are Parameters or Arguments?

Every function will have parentheses after the function name whether there are parameters required or not. In our GetCoffee function, there are two parameters, separated by a comma. This informs the function whether cream or sugar is required. Functions can only return or select items, if you need to Insert, Update, or Delete data you should use a Stored Procedure.

What are the Types of Functions?

There are hundreds of built-in functions within SQL Server. Some functions will request a singular value and return a singular result (Scalar functions), There are some functions that will request a series of values to return a singular result (Aggregate functions). There are also functions that work with sets of records, but those are beyond the scope of this post.

Using a Scalar Function.

An example of a scalar function is the GETDATE() function. This function can be used to return today’s date. This is also an example of a function that does not require parameters. But it still requires using parentheses after the function name. Using the Object Explorer, we can get additional information about the GETDATE() function. Notice that this function returns a singular value of the DATETIME data type.

Using a String Function

Another example of a scalar function is the LEFT() function. This is also an example of a string function. String functions are used to work with a string of characters either from literal text or a column from a table.

The LEFT() function requires two parameters. The first being an expression, or more specifically the string of characters we would like to use for the function. In this case, the text, ‘Deardurff’. The second parameter is how many characters from the left of the text we want to return. In this case, the number 4. When executed, the function will return the varchar or nvarchar data type with the results being, ‘Dear’. In the next post, we will discuss Aggregate Functions in more detail.