How to Call a SQL Server Function using T-SQL ?

How to call SQL Server functions from Transact-SQL scripts? Tutorial on how to call a SQL Server function using T-SQL scripts, with and without parameters. Indeed, the T-SQL syntax for calling a function is different from calling a stored procedure.

Use the SQL Server function in the same way you select data from a table. Learning how to call a user-defined SQL Server function with a T-SQL script is essential for optimizing the maintenance of your database and make data manipulation easier.

In this SQL tutorial, we will examine how to use a user-defined function and how to customize the output of the function to meet specific needs. A user-defined SQL Server function is a Transact-SQL program that accepts input parameters. When called, it performs actions, such as reading a SQL Server table, and returns an output result.

1. Input parameter is not mandatory in a SQL Server function

A function does not necessarily have an input parameter. However, a user-defined SQL Server function returns data, which can be an empty result set, a single row, or multiple columns. For example, let’s call the function created in this tutorial to create an MS SQL function with a script. For example, if you call the built-in function to display the date in SQL Server, the system returns the current date.

SELECT GETDATE();

2. The output result is mandatory in T-SQL functions

Indeed, a function is defined by the fact that it returns a result. To call a function, open SSMS and adapt the function code if necessary. Consider this script to create a function called dbo.ufnDisplayCustomers in T-SQL:

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

To call a SQL Server function, use the following syntax:

SELECT dbo.FunctionName(Parameters);

The function below displays the name of a customer associated with the customer number passed as a parameter. Of course we assume that you have a table with at least these two columns, like in the example.

1. CustomerID
2. Name

/* Expert-Only.com */ 
SELECT [CustomerID]
      ,[Name]
  FROM [Expert-Only].[dbo].[Customers];


/* Expert-Only.com */ 
SELECT dbo.ufnDisplayCustomers(1);

3. Call a SQL Server function and rename the output column

With this second call example, we can also rename the result column to customize the output. To do this, use the generic syntax below:

SELECT dbo.FunctionName(Parameters) AS [NewColumnName];

Below is a practical example, using Customers data, so the function is used to display the customers name based on the customer ID, then the result column is renamed as CustomerName, the same way columns are renamed in SQL Server SELECT queries.

SELECT dbo.ufnDisplayCustomers(1) AS [CustomerName]; 

In this other tutorial, we explain how to modify an existing SQL Server function. For more in-depth and official information on SQL Server user functions, read the MS documentation directly on the Microsoft website.

The English documentation is generally richer and more complete than in other languages, including French. The official Microsoft forum on databases and English support pages are also much more active than other languages.

About calling SQL Server functions from T-SQL scripts

In conclusion, we saw how to call a SQL Server function from a T-SQL script, it is a necessary operation to optimize and especially reuse code efficiently in your database. This tutorial has shown you how to create, call, and customize SQL Server functions.

By mastering these techniques, you will be able to get the most out of your SQL Server database and significantly improve the performance of your applications.