How to call a SQL Server function from a T-SQL script?

How to call a user-defined SQL Server function from a T-SQL script?

Tutorial on how to call a SQL Server function from a T-SQL script using parameters or nor. 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 performance of your database and facilitating data manipulation.

In this tutorial, we will examine how to use a user-defined function and how to customize the output of the function to meet your 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 table, and returns an output result.

1. The 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 funciton to display the date in SQL Server:

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(@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

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.

SELECT dbo.ufnDisplayCustomers(1);

How to create a SQL Server function with a script ?

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 following syntax:

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

Which gives with a concrete example:

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.

Even if in recent years the Microsoft teams have made considerable efforts. And it seems that the documentation in languages other than English has become much richer. Here is finally a complete tutorial to manage functions in T-SQL. The database forum and support pages in English are also much more active than the other languages.

Conclusion on calling functions from SQL Server 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.

Manage SQL Server user defined functions