Modify a SQL Server function with alter function

16 March 2022

How to modify a SQL Server function with Alter Function ? How to update the code of an SQL Server function without deleting it? The solution is to update its definition using the SQL Server Alter Function command. The query updates the function already created in the database. This new code returns not only the customer name, but also the customer city, separated with a dash, the result is stored in the @NameCityCustomer variable.
 

How to modify a SQL Server function with the Alter Function command ?

First of all, it is pretty easy to do, if the original code of the function is available. Indeed, just replace the CREATE FUNCTION keywords by the ALTER FUNCTION ones. To get the actual code of the function, just navigate to the function using SQL Server Management Studio, right click and select Modify. Then the window will show you the script of the function, just edit it and run it.

Use the script below to modify this SQL Function created in a previous article, in order to display the customer name and the customer city, separated by a dash sign.

ALTER FUNCTION dbo.ufnDisplayCustomers(@CustomerNumber INTEGER)
RETURNS NVARCHAR(40)
AS 
BEGIN 
  DECLARE	@NameCityCustomer	 NVARCHAR(20);
  SELECT	@NameCityCustomer = [Name] + '-' + [City] 
  FROM		[dbo].[CUSTOMERS]
  WHERE		[CustomerNumber] = @CustomerNumber;
  RETURN	@NameCityCustomer;
END
GO

This SQL Server query updates the code of an existing function. An easy way to verify that the code works is to select all the code. Start between the first BEGIN and the last END, and then run it, using F5.

For example, to test the first query, declare the @CustomerNumber variable. Like in the next example:

DECLARE  @CustomerNumber  INTEGER = 1;
DECLARE  @CustomerName   NVARCHAR(20);
SELECT  @CustomerName = [Name]
FROM    [dbo].[CUSTOMERS]
WHERE    [CustomerNumber] = @CustomerNumber;
RETURN  @CustomerName;

User-defined functions are, along with stored procedures, the most commonly used programs in T-SQL.

Note that the code of the updated function must also be correct. Otherwise, the system displays an error of this type:

The message in an English version of SSMS : Incorrect syntax near the keyword ‘RETURN’.

All you have to do is correct the syntax and restart the script via the SSMS graphical user interface or with the F5 shortcut which runs the script in the current window.

Related Articles