Working with User-Defined Functions in SQL Server
February 1, 2021
In this tutorial, we will be discussing user-defined functions in SQL Server. More specifically, we will be discussing Scalar functions and Table-Valued functions.
When writing code, one must aim to follow the DRY Principle (Don’t Repeat Yourself). One way to avoid a srepetition of code is to put chunks of code inside functions and invoke them as required.
The concept of functions in SQL is similar to other programming languages like Python. The major difference being the way they are implemented. There are two main types of user-defined functions in SQL based on the data they return:
Scalar functions: These types of functions return a single value, i.e float, int, varchar, datetime, etc.
Table-Valued functions: These functions return tables.
Table of contents
Using functions in Statements.
Using Variables, Conditional Statements inside functions.
A basic understanding of SQL.
A SQL Server with a database.
SQL Server Management Studio to connect to your database.
Below is the definition of a simple function. It takes in two numbers and returns their sum. Since this function returns a number, it is a scalar function.
CREATE FUNCTION scalar_func ( @a AS INT, -- parameter a @b AS INT -- parameter b ) RETURNS INT -- return type AS BEGIN RETURN @a + @b -- return statement END;
We use the
Create functioncommand to define functions. It is followed by the name of the function. In the above example, the name of the function is
We need to declare the parameters of the function in the following format.
@VariableName AS Data Type
In our above example, we have defined two integer parameters
The return type of the result has to be mentioned below the definition of the parameters. In the above example, we are returning the sum that is an integer.
After the return statements, we create a
BEGIN ... ENDblock that contains the logic of our function. Although in this case, we have a single return statement, we don’t need a
BEGIN ... ENDblock.
Before creating a table-valued function, we will create a simple table.
-- Creating new table CREATE TABLE TEST( num1 INT, num2 INT ); -- Inserting values into new table INSERT INTO TEST VALUES (1,2), (2,3), (4,5);
The table contains 2 columns. We will create a function that returns a new table with an extra column. This extra column will contain the sum of numbers in the column
num1 and column
CREATE FUNCTION table_valued_func() RETURNS TABLE AS RETURN -- statement to calculate sum SELECT num1 , num2, num1 + num2 AS 'SUM' FROM TEST;
The function above does not take in any parameter.
The SQL statement simply calculates the sum and stores it in a new column named
Using functions in Statement
-- invoking previously created scalar function SELECT dbo.scalar_func(1,2);
When using functions in statements, we will need to prefix our functions with the database schema it is associated with. The default schema in Microsoft SQL Server is
dbo. If the database schema is not mentioned, SQL will give an error,
Since the function returns a table, we will need to select the columns we are interested in.
-- invoking previously created table valued function SELECT * FROM dbo.table_valued_func();
Like scalar functions, we will need to mention the database schema.
The syntax to update/delete scalar and table-valued functions are the same.
We will update our table-valued function to add 10 to the existing sum and change the name of the column to
ALTER FUNCTION table_valued_func() RETURNS TABLE AS RETURN -- updating statement to add 10 to sum SELECT num1 , num2, num1 + num2 + 10 AS 'NEW_SUM' FROM TEST;
Alter Keyword is used to update the function.
-- dropping previously created scalar function DROP FUNCTION dbo.scalar_func; -- dropping previously created tabular function DROP FUNCTION dbo.table_valued_func;
Note: Do not put parenthesis after the function name.
Using variables and conditional statements inside functions
Below is the syntax to declare and initialize variables.
-- declaring integer variable DECLARE @result AS INT; -- initializing created varaible SET @result = @a + @b;
DECLARE keyword is used to create a variable and the
SET keyword is used to initialize a variable.
We have an example below of a scalar function using a variable.
CREATE FUNCTION scalar_func ( @a AS INT, @b AS INT ) RETURNS INT AS BEGIN -- using variables inside function DECLARE @result AS INT SET @result = @a + @b RETURN @a + @b END;
The syntax for
IF...ELSE Statements is like
IF...ELSE statements in Python or C++.
DECLARE @num AS INT; SET @num = 4; -- if condition IF @num % 2 = 0 BEGIN SELECT 'Number is Even' END -- else condition ELSE BEGIN SELECT 'Number is Odd' END
The piece of code above checks whether the value in variable
num is even or odd. Based on the value, the
IF or the
ELSE Block is executed.
Listed below is a function using an
CREATE FUNCTION is_even(@num AS INT) RETURNS BIT AS BEGIN DECLARE @result AS BIT -- set variable to 1 if number is even IF @num % 2 = 0 SET @result = 1 -- set variable to 0 if number is odd ELSE SET @result = 0 RETURN @result END;
When you are dealing with multiple if statements, it is better to use case statements. They make your code easier to read. Below is the general syntax for case statements.
CASE WHEN condition1 THEN result1 WHEN condition2 THEN result2 . . . ELSE result END
Like switch cases, all the cases are checked and if multiple cases are satisfied, the respective code blocks will be executed.
Below we have a function that uses case statements.
CREATE FUNCTION is_greater ( @a AS INT, @b AS INT ) RETURNS VARCHAR(30) AS BEGIN RETURN( 'A is' + CASE -- Case 1 WHEN @a > @b THEN 'Greater than' -- Case 2 WHEN @a < @b THEN 'Smaller than' ELSE 'Equal to' END + 'B') END;
It compares two integers and returns a string based on the comparison result.
As I mentioned above, try to follow the DRY Principle while writing SQL Statements. When you see the same piece of code being used in multiple statements, consider putting it inside a function. Functions make your statements look much cleaner and shorter.
Peer Review Contributions by: Saiharsha Balasubramaniam
About the authorRahul Banerjee
Rahul Banerjee is a Computer Engineering Undergrad student at the University of Toronto. He is currently doing an Internship at Ernst and Young. He is a Data Science enthusiast interested in writing tech articles on Medium. If he is not working or writing articles, he is usually binge-watching shows on Netflix.