How to create a SQL Server function with a script ?

Example of T-SQL code to create a simple SQL Server user defined function.

Tutorial to create a simple SQL Server function with a T-SQL script. You can use the code provided as a syntax checklist or an example to copy and paste. In fact, the script creates a SQL function with a customer number as a parameter and the customer’s name as the result.

T-SQL functions, like stored procedures, are at the heart of Microsoft database programming. They allow to manage the intelligence of the databases and to reuse the code with a single keyword. The basic principle is to reuse code instead of programming the same thing over and over again.

1. Add a source table to use in the function

To start, create the customers table with the code available on this page with the code for creating the Customers example table. The code allows you to have the source table used by the view. The source table in your case will be adapted with your own objects. This type of function is a user defined function. The name is stored in the return variable @CustomerName. Use the sample table creation code available here :

-- 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;

This sample function in the next section accepts as input the customer number and returns the customer name.

2. Create the function using a T-SQL script

The 6 important steps in the SQL Server function declaration below are the following. 

1. We define the entry parameter as an Integer, i.e. @CustomerNumber.
2. We declare the return type, here it is the NVARCHAR(20) data type.
3. Then we declare the variable @CustomerName, also with the NVARCHAR(20) data type.
4. Select the Customer name from the CUSTOMERS table using the @CustomerNumber variable. Indeed, a customer as a unique customer number so we use it as the key for the selection.
5. In the SELECT query, the customer name is stored into the @CustomerName variable.
6. Then we return customer name using the variable.

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

Once created, you can easily call and test the SQL Server function using this script.

SELECT dbo.ufnDisplayCustomers(1);

Note that the syntax for calling a function is the same as for a table. The difference is the passing of the parameter. For a function with no parameter then the parentheses remain empty. To go further, there are different types of SQL functions to study. This article shows how to create a SQL Server function, check here this article on how to create a text list of files available in one folder in cmd.

Conclusion on creating a function with T-SQL

In conclusion, learning how to create a simple SQL Server function with a T-SQL script is a fundamental skill for database developers and administrators. This tutorial demonstrated the process of creating a user-defined function that takes a customer number as a parameter and returns the corresponding customer’s name. By leveraging T-SQL functions, you can encapsulate complex logic and promote code reusability across all your database applications. Which one of the most important good practice in progamming.

Modify a SQL Server function with alter function