SQL Server Create Function

How to write an SQL Server Create Function query? This SQL Server function takes as a parameter the customer number and returns the customer name. Indeed, the function stores the customer name in the @CustomerName variable, and it returns it as a result.

 

SQL Server Create 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 test the function:

SELECT dbo.ufnDisplayCustomers(1);

 

 

 

Be the first to comment

Leave a Reply