Alter User Defined Functions in SQL

In our previous article, we explained to you about creating User Defined Functions. Let us see how to alter User Defined Functions in SQL Server, such as Renaming, Modifying, and deleting the existing UDFs.

How to see User Defined Functions in SQL?

To view the existing functions in SQL Server, Please select the Database that has UDFs. From the below screenshot, you can observe that our [SQL Tutorial] database has all the functions that we created in our User Defined Functions article.

To execute the existing functions, Please select the function that you want to execute (AverageSale). Next, right-click on it and select the Script Function as -> SELECT To -> New Query Window Editor

Once you select the New Query Window Editor option, Select from function query will return automatically by the SSMS

Properties of SQL User Defined Functions using SSMS

First, Right-click on the required function name and select the SQL Server properties option.

Once you click on the Properties option, a new window called Function Properties opened. The following are the list of SQL Server Function Properties, and they are:

• Database: It shows the name of the database that contains the specified function. Here, it is AverageSale
• Server: The name of the current SQL Server Instance.
• User: It shows the name of the current user
• Created Date: It displays the function created date.
• Name: It shows the name of the current Function. i.e., AverageSale
• Schema: It displays the schema that we used for this function.
• System Object: It shows Boolean value True or False, indicating whether the current function is a System Object or Not.
• ANSI NULLs: Displays Boolean value True or False, indicating whether the object was created with ANSI NULLs or Not.
• Function type: It show’s whether the function is SCALAR or Table-Valued Functions

Properties of SQL User Defined Functions using Query

How to find the definition of a SQL User Defined Functions using the OBJECT_DEFINITION.

-- It Returns the AverageSale function Definition  
SELECT OBJECT_DEFINITION (OBJECT_ID('dbo.AverageSale')) AS ObjectDefinition;  
GO

OUTPUT

The following query returns the remaining properties of SQL UDFs.

-- It Returns the AverageSale function Name & it's Properties  
SELECT smo.OBJECT_ID,   
   OBJECT_NAME(smo.OBJECT_ID) AS OBJECT_NAME,   
   Obj.TYPE,   
   Obj.TYPE_DESC,  
   obj.create_date,
   smo.uses_ansi_nulls,  
   smo.uses_quoted_identifier,  
   smo.is_schema_bound,  
   smo.execute_as_principal_id  
FROM sys.sql_modules AS smo  
JOIN sys.objects AS Obj ON smo.OBJECT_ID = Obj.OBJECT_ID  
WHERE smo.OBJECT_ID = OBJECT_ID('dbo.AverageSale')  
ORDER BY Obj.TYPE  
GO

OUTPUT

TIP: You can also return the Function definition by adding: smo.DEFINITION inside the Select Statement.

Rename User Defined Functions in SQL Server

To rename the SQL User Defined Functions using the Microsoft SQL Server Management Studio, right-click on the function that you want to modify (AverageSale) and select the Rename option

Once you click on the Rename option, SSMS allows us to rename as per our requirements.

Modify User Defined Functions in SQL Server

The following examples will help you understand modifying or altering the SQL User Defined Functions using the Microsoft SQL Server Management Studio (SSMS) and query.

Alter UDFs using SSMS

To modify the UDFs using the SQL Server Management Studio, right-click on the function (CustomerbyDepartmnet) that you want to change, and select the Modify option

Once you choose the modify option, a new query window will open with the following query. You can edit as per your requirement.

Alter SQL User Defined Functions using Query

Let me use the ALTER FUNCTION to modify the existing function. For demonstration purpose, we are concatenating the First Name and Last Name as NAME

ALTER FUNCTION [dbo].[CustomerbyDepartment] (@profession VARCHAR(50))
  RETURNS TABLE
  AS
     RETURN (
 SELECT  [FirstName] + ' ' + [LastName] AS Name
 ,[Occupation]
 ,[Education]
 ,dept.DepartmentName AS Department
 ,[YearlyIncome] AS Income
 ,[Sales]
   FROM [MyEmployees Table]
 INNER JOIN 
 Department AS dept ON
 Dept.[id] = [MyEmployees Table].DeptID
 WHERE [Occupation] = @profession
 )

Let us see the Output

SELECT * FROM [dbo].[CustomerbyDepartment] ('Management')
GO

OUTPUT

From the above screenshot, you can see the function is returning the NAME instead of First Name and Last Name.

Delete User Defined Functions in SQL Server

How to delete the SQL User Defined Functions using the Microsoft SQL Server Management Studio (SSMS), and T-SQL Query. Before we get into the examples, remember the limitations:

• SQL Server will not allow you to delete the function if there are any references from Functions, or Views to this function.
• We cannot delete, if there are any references from Computed Columns, CHECK, or Default Contrarians to this function.

To demonstrate the delete operation, we added two more scalar functions. Our task is to delete the EmployeeSale and SaleEmployees functions.

Delete UDFs using SQL Server Management Studio

To delete the UDFs using the SQL Server Management Studio, right-click on the function name and click the Delete option. Here, we want to delete SaleEmployees (scalar) function

Choosing the delete option opens a Delete Object window. Click on the Show Dependencies button to check the dependencies, and then click OK to delete that function.

Delete Functions using SQL Query

How to use SQL Server DROP FUNCTION to delete the User Defined Functions

DROP FUNCTION [dbo].[EmployeesSale]
GO

TIP: It is good practice to check, whether the function exists in the database or not using IF OBJECT_ID (N’EmployeeSale’, N’IF’) IS NOT NULL

Let us see what happens when we call the deleted function.

From the above screenshot, see we don’t have EmployeeSale function in our Object explorer, and querying on the function is throwing an error.