SQL Server DROP FUNCTION

Summary: in this tutorial, you will learn how to remove an existing user-defined function by using the SQL Server DROP FUNCTION statement.

Introduction to SQL Server DROP FUNCTION statement

To remove an existing user-defined function created by the CREATE FUNCTION statement, you use the DROP FUNCTION statement as follows:

DROP FUNCTION [ IF EXISTS ] [ schema_name. ] function_name;
Code language: SQL (Structured Query Language) (sql)

In this syntax:

 IF EXISTS

The IF EXISTS option allows you to drop the function only if it exists. Otherwise, the statement does nothing. If you attempt to remove a non-existing function without specifying the IF EXISTS option, you will get an error.

 schema_name

The schema_name specifies the name of the schema to which the user-defined function which you wish to remove belongs. The schema name is optional.

 function_name

The function_name is the name of the function that you want to remove.

Notes

If the function that you want to remove is referenced by views or other functions created using the WITH SCHEMABINDING option, the DROP FUNCTION will fail.

In addition, if there are constraints like CHECK or DEFAULT and computed columns that refer to the function, the DROP FUNCTION statement will also fail.

To drop multiple user-defined functions, you specify a comma-separated list of function names in after the DROP FUNCTION clause as follows:

DROP FUNCTION [IF EXISTS] 
    schema_name.function_name1, 
    schema_name.function_name2,
    ...;Code language: SQL (Structured Query Language) (sql)

SQL Server DROP FUNCTION example

We will use the order_items from the sample database for the demonstration:

SQL Server DROP FUNCTION – a simple example

The following example creates a function that calculates discount amount from quantity, list price, and discount percentage:

CREATE FUNCTION sales.udf_get_discount_amount (
    @quantity INT,
    @list_price DEC(10,2),
    @discount DEC(4,2) 
)
RETURNS DEC(10,2) 
AS 
BEGIN
    RETURN @quantity * @list_price * @discount
END
Code language: SQL (Structured Query Language) (sql)

To drop the sales.udf_get_discount_amount function, you use the following statement:

DROP FUNCTION IF EXISTS sales.udf_get_discount_amount;
Code language: SQL (Structured Query Language) (sql)

SQL Server DROP FUNCTION with SCHEMABINDING example

The following example recreates the function sales.udf_get_discount_amountusing the WITH SCHEMABINDING option:

CREATE FUNCTION sales.udf_get_discount_amount (
    @quantity INT,
    @list_price DEC(10,2),
    @discount DEC(4,2) 
)
RETURNS DEC(10,2) 
WITH SCHEMABINDING
AS 
BEGIN
    RETURN @quantity * @list_price * @discount
END
Code language: SQL (Structured Query Language) (sql)

And the following statement creates a view that uses the sales.udf_get_discount_amount function:

CREATE VIEW sales.discounts
WITH SCHEMABINDING
AS
SELECT
    order_id,
    SUM(sales.udf_get_discount_amount(
        quantity,
        list_price,
        discount
    )) AS discount_amount
FROM
    sales.order_items i
GROUP BY
    order_id;
Code language: SQL (Structured Query Language) (sql)

Now, if you try to remove the sales.udf_get_discount_amount function, you will get an error:

DROP FUNCTION sales.udf_get_discount_amount;
Code language: SQL (Structured Query Language) (sql)

SQL Server returns the following error:

Cannot DROP FUNCTION 'sales.udf_get_discount_amount' because it is being referenced by object 'discounts'.
Code language: SQL (Structured Query Language) (sql)

If you want to remove the function, you must drop the sales.discounts view first:

DROP VIEW sales.discounts;
Code language: SQL (Structured Query Language) (sql)

And then drop the function;

DROP FUNCTION sales.udf_get_discount_amount;
Code language: SQL (Structured Query Language) (sql)

In this tutorial, you have learned how to use the SQL Server DROP FUNCTION to remove one or more existing user-defined functions.