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.
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, which 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 could use a Stored Procedure.
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.
An example of a scalar function is the GETDATE() function that can be used to return today’s date. This is also an example of a function that does not require parameters, but 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.
Another example of a scalar function is the LEFT() function that 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.