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 Rename, modify, and delete 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 database has all the functions that we created in our User Defined Functions article.

To execute the existing functions, Please select the one 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 query will return automatically by the SSMS

Properties of SQL User Defined Functions using SSMS

First, Right-click on the required 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 Instance.
• User: It shows the name of the current user
• Created Date: It displays the created date.
• Name: It shows the name of the current one. i.e., AverageSale
• Schema: It displays the schema that we used for it.
• System Object: It shows Boolean value True or False, indicating whether the current one 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

The following query returns the remaining properties of SQL UDFs.

-- It Returns the AverageSale 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

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

Rename User Defined Functions in SQL Server

To rename the User Defined Functions using the Microsoft SQL Server Management Studio, right-click on the one 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 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. 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

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

Delete User Defined Functions in SQL Server

How to delete the User Defined Functions using the Microsoft SQL Server Management Studio (SSMS), and transact 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 it or Views to it.
• We cannot delete, if there are any references from Computed Columns, CHECK, or Default Contrarians to it.

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

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.

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.

Delete Functions using SQL Query

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

DROP FUNCTION [dbo].[EmployeesSale]
GO

TIP: It is good practice to check, whether it 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 one.

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