{"id":929,"date":"2018-10-14T10:45:53","date_gmt":"2018-10-14T03:45:53","guid":{"rendered":"http:\/\/www.sqlservertutorial.net\/?page_id=929"},"modified":"2020-04-11T20:13:08","modified_gmt":"2020-04-11T13:13:08","slug":"sql-server-drop-function","status":"publish","type":"page","link":"https:\/\/www.sqlservertutorial.net\/sql-server-user-defined-functions\/sql-server-drop-function\/","title":{"rendered":"SQL Server DROP FUNCTION"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you will learn how to remove an existing user-defined function by using the SQL Server DROP FUNCTION<\/code> statement.<\/p>\n\n\n\n

Introduction to SQL Server DROP FUNCTION statement #<\/a><\/h2>\n\n\n\n

To remove an existing user-defined function created by the CREATE FUNCTION<\/a><\/code> statement, you use the DROP FUNCTION<\/code> statement as follows:<\/p>\n\n\n

DROP<\/span> FUNCTION<\/span> [ IF<\/span> EXISTS<\/span> ] [ schema_name. ] function_name;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
In this syntax:<\/p>\n\n\n\n
 IF EXISTS #<\/a><\/h3>\n\n\n\n
The IF EXISTS<\/code> 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<\/code> option, you will get an error.<\/p>\n\n\n\n
 schema_name #<\/a><\/h3>\n\n\n\n
The schema_name<\/code> specifies the name of the schema to which the user-defined function which you wish to remove belongs. The schema name is optional.<\/p>\n\n\n\n
 function_name #<\/a><\/h3>\n\n\n\n
The function_name<\/code> is the name of the function that you want to remove.<\/p>\n\n\n\n
Notes #<\/a><\/h3>\n\n\n\n
If the function that you want to remove is referenced by views or other functions created using the WITH SCHEMABINDING<\/code> option, the DROP FUNCTION<\/code> will fail.<\/p>\n\n\n\n
In addition, if there are constraints like CHECK<\/code> or DEFAULT<\/code> and computed columns that refer to the function, the DROP FUNCTION<\/code> statement will also fail.<\/p>\n\n\n\n
To drop multiple user-defined functions, you specify a comma-separated list of function names in after the DROP FUNCTION<\/code> clause as follows:<\/p>\n\n\n
DROP<\/span> FUNCTION<\/span> [IF<\/span> EXISTS<\/span>] \n    schema_name.function_name1, \n    schema_name.function_name2,\n    ...;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
SQL Server DROP FUNCTION example #<\/a><\/h2>\n\n\n\n
We will use the order_items<\/code> from the sample database<\/a> for the demonstration:<\/p>\n\n\n\n
\"order_items\"<\/figure>\n\n\n\n
SQL Server DROP FUNCTION – a simple example #<\/a><\/h3>\n\n\n\n
The following example creates a function that calculates discount amount from quantity, list price, and discount percentage:<\/p>\n\n\n
CREATE<\/span> FUNCTION<\/span> sales.udf_get_discount_amount (\n    @quantity INT<\/span>,\n    @list_price DEC<\/span>(10<\/span>,2<\/span>),\n    @discount DEC<\/span>(4<\/span>,2<\/span>) \n)\nRETURNS<\/span> DEC<\/span>(10<\/span>,2<\/span>) \nAS<\/span> \nBEGIN<\/span>\n    RETURN<\/span> @quantity * @list_price * @discount\nEND<\/span>\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
To drop the sales.udf_get_discount_amount<\/code> function, you use the following statement:<\/p>\n\n\n
DROP<\/span> FUNCTION<\/span> IF<\/span> EXISTS<\/span> sales.udf_get_discount_amount;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
SQL Server DROP FUNCTION with SCHEMABINDING example #<\/a><\/h3>\n\n\n\n
The following example recreates the function sales.udf_get_discount_amount<\/code>using the WITH SCHEMABINDING<\/code> option:<\/p>\n\n\n
CREATE<\/span> FUNCTION<\/span> sales.udf_get_discount_amount (\n    @quantity INT<\/span>,\n    @list_price DEC<\/span>(10<\/span>,2<\/span>),\n    @discount DEC<\/span>(4<\/span>,2<\/span>) \n)\nRETURNS<\/span> DEC<\/span>(10<\/span>,2<\/span>) \nWITH<\/span> SCHEMABINDING\nAS<\/span> \nBEGIN<\/span>\n    RETURN<\/span> @quantity * @list_price * @discount\nEND<\/span>\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
CREATE<\/span> VIEW<\/span> sales.discounts\nWITH<\/span> SCHEMABINDING\nAS<\/span>\nSELECT<\/span>\n    order_id,\n    SUM<\/span>(sales.udf_get_discount_amount(\n        quantity,\n        list_price,\n        discount\n    )) AS<\/span> discount_amount\nFROM<\/span>\n    sales.order_items i\nGROUP<\/span> BY<\/span>\n    order_id;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Now, if you try to remove the sales.udf_get_discount_amount<\/code> function, you will get an error:<\/p>\n\n\n
DROP<\/span> FUNCTION<\/span> sales.udf_get_discount_amount;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
SQL Server returns the following error:<\/p>\n\n\n
Cannot DROP<\/span> FUNCTION<\/span> 'sales.udf_get_discount_amount'<\/span> because it is<\/span> being referenced<\/span> by<\/span> object<\/span> 'discounts'<\/span>.\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
DROP<\/span> VIEW<\/span> sales.discounts;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
And then drop the function;<\/p>\n\n\n
DROP<\/span> FUNCTION<\/span> sales.udf_get_discount_amount;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
In this tutorial, you have learned how to use the SQL Server DROP FUNCTION<\/code> to remove one or more existing user-defined functions.<\/p>\n
\n\t
\n\t\t
Was this tutorial helpful?<\/div>\n\t\t
\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t Yes <\/span>\n\t\t\t<\/button>\n\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t No <\/span>\n\t\t\t<\/button>\n\t\t<\/div>\n\t<\/header>\n\n\t
\n\t\t
\n\t\t\t
<\/div>\n\t\t\t\n\t\t\t
If you want to remove the function, you must drop<\/a> the sales.discounts<\/code> view first:<\/p>\n\n\n
And the following statement creates a view<\/a> that uses the sales.udf_get_discount_amount<\/code> function:<\/p>\n\n\n