SQL Server user-defined functions are very useful when you start T-SQL development on SQL Server. 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 user-defined 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 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:
- Create the Customers sample table
- Create an SQL Server user-defined function
- Modify an SQL Server function
- Delete an SQL Server function
- Test the existence of a function before deletion
- 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.
You just have to copy/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;
2. How to create an SQL Server user-defined 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 modify an 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] + '-' + [VILLE] FROM [dbo].[CUSTOMERS] WHERE [CustomerNumber] = @CustomerNumber; RETURN @NameCityCustomer; END GO
4. How to delete SQL Server user-defined functions?
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 test if a function exists before to delete it?
To avoid the error above, add an existence check in your 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
6. How to call SQL Server user-defined functions ?
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 bracke. 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)“.
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 user-defined functions? you can read the Microsoft SQL Server official documentation, available here: