SQL Server 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 an existing object using SQL Server Alter Function query?

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.

The script below modfies 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;

Be the first to comment

Leave a Reply