How to create a SQL Server function ?

How to create a simple SQL Server function ? This example of Transact-SQL code is to be copied and pasted. And use it as a syntax checklist. In fact, here is a script for creating an 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. Indeed, 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.

How to create a function with a SQL Server script ?

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.

This function accepts as input the customer number and returns the customer name. 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;

The different steps in this SQL Server function are the following. 

  • Fist of all, we declare the return type.
  • Then we declare the variable @CustomerName with an NVARCHAR data type.
  • Select the Customer name from the CUSTOMERS table using the CustomerName. Indeed, a customer as a unique customer number.
  • After that, the customer name is stored into the @CustomerName variable.
  • Then we return customer name.
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 function:

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.

What is the difference between a function and a SQL Server stored procedure?

The main difference between a function and a SQL Server stored procedure is that a function takes an input parameter and returns a result. Whereas a stored procedure does not necessarily return a result or even take an input parameter. A stored procedure is a set of one or multiple T-SQL statements executed as one batch.

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.