SQL Server user-defined functions example queries

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:

  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.
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)“.

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 user-defined functions? you can read the Microsoft SQL Server official documentation, available here:

https://docs.microsoft.com/en-us/sql/relational-databases/user-defined-functions/user-defined-functions?view=sql-server-2017

 

 

Be the first to comment

Leave a Reply