Alter User Defined Functions in SQL

In our previous article, we explained you about creating User Defined Functions. In this article we will explain you, How to alter User Defined Functions in SQL Server such as: Renaming, Modifying, and deleting the existing UDFs.

View User Defined Functions in SQL

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

To execute, or to select the existing functions, Please navigate to the function that you want to execute (AverageSale), and right click on the function name will open the context menu. Here, select the Script Function as -> SELECT To -> New Query Window Editor as shown below

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

Properties of SQL User Defined Functions using SSMS

First, Navigate to required Database and then select the required function. Right Click on the function name will open the Context Menu with different options. Please select the properties option as we shown below.

Once you click on the Properties option, a new window called Function Properties will be opened as shown below. The following are the list of properties shown in the Function Properties windows, 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 date the function was created.
• 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

In this example we will show you, How to find the definition of a SQL User Defined Functions using the Transact-SQL query.

SQL CODE

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

OUTPUT

Following query will return the remaining properties of UDFs.

SQL CODE

USE [SQL Tutorial]
GO

-- 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

In this example we will show you, How to rename the SQL User Defined Functions using the Microsoft SQL Server Management Studio (SSMS). In order to rename, Please navigate to the function that you want to modify (AverageSale), and right click on the function name will open the context menu. Here, select the Rename option as shown below

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 the steps involved in modifying the SQL User Defined Functions using the Microsoft SQL Server Management Studio (SSMS).

Modify UDFs using SSMS

In order to modify the UDFs using the SQL Server Management Studio, Please navigate to the function that you want to modify (CustomerbyDepartmnet), and right click on the function name will open the context menu. Here, select the Modify option as shown below

Once you select the modify option, a new query window will be opened with following query. You can modify as per your requirement.

Modify SQL User Defined Functions using Query

In this example, we will show you the steps involved in modifying the existing function. To do so, Click New query and return the following query.

For demonstrate, we are concatenating the First Name and Last Name as NAME

SQL CODE

-- Modify User Defined Functions in SQL Server
USE [SQL Tutorial]
GO

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

SQL CODE

USE [SQL Tutorial]
GO

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

OUTPUT

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

Delete User Defined Functions in SQL Server

The following examples will show you, How to delete the SQL User Defined Functions using the Microsoft SQL Server Management Studio (SSMS), and Transact-SQL Query. Before we get into the examples, remember, below shown 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 have added two more scalar function as shown below. Our task is to delete the EmployeeSale, and SaleEmployees function present in the SQL Tutorial database.

Delete UDFs using SQL Server Management Studio

In this example we will show you, How to delete the UDFs using the SQL Server Management Studio. In order to do so, Please navigate to the function that you want to delete, and right click on the function name will open the context menu. You can click or select the Delete option as shown below

For this demonstration we want to delete SaleEmployees (scalar) function

Once you select the delete option, a Delete Object window will be opened as shown below. Click on the show Dependencies button to check the dependencies, and then click OK to delete the function.

Delete Functions using SQL Query

In this example we will show you, How to delete the User Defined Functions using the T-SQL query.

SQL CODE

-- Deleteing User Defined FUnctions in SQL Server
USE [SQL Tutorial]
GO

DROP FUNCTION [dbo].[EmployeesSale]
GO

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

Let us see what will happen, when we call the deleted function.

From the above screenshot you can observe that, we don’t have EmployeeSale function in our Object explorer, and also querying the function is throwing an error.

Thank You for Visiting Our Blog