Manage SQL Server user defined functions

How to manage SQL Server user defined functions ? SQL Server user-defined functions are very useful when starting T-SQL development. You need to use them very often so you have to learn the right syntaxes and avoid errors. At the beginning it can be tricky and frustrating because it’s binary, it work or don’t work, programming T-SQL functions is an exact science.

There is two types of SQL Server functions which are the user-defined functions and the built-in functions. In this article we’ll see how to manage the user defined functions, in other words how to create, modify and delete functions.

How to manage a SQL Server user defined functions ?

First things first, what is an SQL Server function?

An SQL Server function is a Transact-SQL program that takes input parameters, performs actions, like reading a table, or performing a calculation and returns the result as an output. The input and output is the main difference between functions and stored procedures. Indeed, functions returns results when stored procedure don’t necessarily do.

Here are code samples to manage SQL Server functions:

1. Create the Customers sample table
2. Create an SQL Server user-defined function
3. Modify an SQL Server function
4. Delete an SQL Server function
5. Test the existence of a function before deletion
6. Call a function

1. Create the Customers sample table used by the function

Before you start using the SQL queries to manage the functions, create these sample Customers table, all instructions are available here. Just copy and paste and execute the script:

-- If the Customers table already exists, then we drop it
IF exists(   
	SELECT  1 FROM sys.objects
	WHERE   object_id = object_id(N'[dbo].[CUSTOMERS]') AND type in (N'U')
)
BEGIN
	DROP TABLE [dbo].[CUSTOMERS]
END
GO

-- We Create the CUSTOMERS table with the Column [Name] declared as Unique
-- The UNIQUE keyword don't allow to have two different customers with the same name
CREATE TABLE [dbo].[CUSTOMERS] (
	[CustomerNumber] 	 INTEGER IDENTITY(1,1),
	[Name] 	NVARCHAR(20) UNIQUE,
	[City] 	NVARCHAR(20)
)
GO

-- Data insertion for the function example
INSERT INTO dbo.CUSTOMERS ( [Name], [City] ) VALUES ( N'John', 'New York');
INSERT INTO dbo.CUSTOMERS ( [Name], [City] ) VALUES ( N'Ahmed', 'Los Angeles');
INSERT INTO dbo.CUSTOMERS ( [Name], [City] ) VALUES ( N'Jane', 'Miami');
INSERT INTO dbo.CUSTOMERS ( [Name], [City] ) VALUES ( N'Lee', 'Chicago');
INSERT INTO dbo.CUSTOMERS ( [Name], [City] ) VALUES ( N'Diana', 'San Francisco');

-- Let's verify the inserted lines for our 5 customers
SELECT * 
FROM dbo.CUSTOMERS;

To go further for the tables, these articles shows examples on how to calculate time between two dates in T-SQL.

2. How to create a SQL Server function?

This function takes as a parameter the customer number and returns the customer name. The result is stored in the @ClientName variable.

CREATE FUNCTION dbo.ufnDisplayCustomers(@CustomerNumber INTEGER)
RETURNS NVARCHAR(20)
AS 
BEGIN 
  DECLARE	@ClientName	AS NVARCHAR(20);
  SELECT	@ClientName = [Name]
  FROM		[dbo].[CUSTOMERS]
  WHERE		[CustomerNumber] = @CustomerNumber;
  RETURN	@ClientName;
END
GO

3. How to alter or modify a SQL Server function?

To update the code of your function without deleting it, you can modify it or alter it using the ALTER command. This altered version is updating the function we just created above, so this time it’s returning not only the customer name, but also the customer city, separated with a dash, the result is stored in the @NameCityCustomer variable.

ALTER FUNCTION dbo.ufnDisplayCustomers(@CustomerNumber INTEGER)
RETURNS NVARCHAR(40)
AS 
BEGIN 
  DECLARE	@NameCityCustomer	AS NVARCHAR(20);
  SELECT	@NameCityCustomer = [Name] + '-' + [City] 
  FROM		[dbo].[CUSTOMERS]
  WHERE		[CustomerNumber] = @CustomerNumber;
  RETURN	@NameCityCustomer;
END
GO

4. How to drop or delete a SQL Server function?

This SQL command delete the SQL Server function, nevertheless it’s not checking if the function exists.

DROP FUNCTION ufnDisplayCustomers;
GO

If the function was not actually existing in your databse, then you face this error:

Msg 3701, Level 11, State 5, Line 1
Cannot drop the function ‘ufnDisplayCustomers’, because it does not exist or you do not have permission.

5. How to check if user defined function exists in Sql Server ?

To avoid the error above, add an existence check in the SQL command. If the function actually exist in the database then it’s dropped, otherwise the “drop function” command is not executed.

IF OBJECT_ID (N'dbo.ufnDisplayCustomers', N'FN') IS NOT NULL  
    DROP FUNCTION ufnDisplayCustomers;  
GO

The DROP FUNCTION IF EXISTS option

Since SQL Server 2016, it more easy to drop a database object. Simply use these one line code:

DROP FUNCTION IF EXISTS ufnDisplayCustomers;

6. How to call a SQL Server user-defined function ?

Finally, to call an SQL Server function, you can use a SELECT statement followed by the name of the function, and also the input parameter value in brackets. In this query, the input parameter is the customer number you are displaying.

This first version is returning “John” and the column name is the default one “(No column name)“.

SELECT dbo.ufnDisplayCustomers(1);

To finish, this second version of the command is similar and it names explicitly the column as “CUSTOMER“.

SELECT dbo.ufnDisplayCustomers(1) AS CUSTOMER;

To read more informations about the SQL Server functions, check out and read the Microsoft SQL Server official documentation, available here: