How to create a SQL Server function?

How to create a SQL Server function? For example, the SQL Server function below takes the customer number as a parameter and returns the customer name. Indeed, the function stores the customer name in the Customer Name variable, and it returns it as a result.

How to create a SQL Server function?

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.

You can use the creation query of the Customers table here.

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

This article shows how to create a SQL Server function, check here 2 ways on how to call an existing SQL Server function.

Be the first to comment

Leave a Reply