modify-user-defined-functions.md

title	Modify User-defined Functions | Microsoft Docs
ms.custom
ms.date	03/20/2017
ms.prod	sql-server-2016
ms.reviewer
ms.suite
ms.technology	dbe-udf
ms.tgt_pltfrm
ms.topic	article
ms.assetid	891c37b3-cb72-411f-9937-ee87e6d95f34
caps.latest.revision	7
author	JennieHubbard
ms.author	jhubbard
manager	jhubbard

Modify User-defined Functions

You can modify user-defined functions in [!INCLUDEssCurrent] by using [!INCLUDEssManStudioFull] or [!INCLUDEtsql]. Modifying user-defined functions as described below will not change the functions’ permissions, nor will it affect any dependent functions, stored procedures, or triggers.

In This Topic

• Before you begin:

  Limitations and Restrictions

  Security

• To modify a user-defined function, using:

  SQL Server Management Studio

  Transact-SQL

Before You Begin

Limitations and Restrictions

ALTER FUNCTION cannot be used to perform any of the following actions:

• Change a scalar-valued function to a table-valued function, or vice versa.

• Change an inline function to a multistatement function, or vice versa.

• Change a Transact-SQL function to a CLR function, or vice-versa.

Security

Permissions

Requires ALTER permission on the function or on the schema. If the function specifies a user-defined type, requires EXECUTE permission on the type.

Using SQL Server Management Studio

To modify a user-defined function

1. Click on the plus sign next to the database that contains the function you wish to modify.

2. Click on the plus sign next to the Programmability folder.

3. Click the plus sign next to the folder that contains the function you wish to modify:

   • Table-valued Function

   • Scalar-valued Function

   • Aggregate Function

4. Right-click the function you want to modify and select Modify.

5. In the Query Window, make the necessary changes to the ALTER FUNCTION statement.

6. On the File menu, click Savefunction_name.

Using Transact-SQL

To modify a user-defined function

1. In Object Explorer, connect to an instance of [!INCLUDEssDE].

2. On the Standard bar, click New Query.

3. Copy and paste the following example into the query window and click Execute.

   -- Scalar-Valued Function  
   USE [AdventureWorks2012]  
   GO  
   ALTER FUNCTION [dbo].[ufnGetAccountingEndDate]()  
   RETURNS [datetime]   
   AS   
   BEGIN  
       RETURN DATEADD(millisecond, -2, CONVERT(datetime, '20040701', 112));  
   END;  
   

   -- Table-Valued Function   
   USE [AdventureWorks2012]  
   GO  
   ALTER FUNCTION [dbo].[ufnGetContactInformation](@PersonID int)  
   RETURNS @retContactInformation TABLE   
   (  
       -- Columns returned by the function  
       [PersonID] int NOT NULL,   
       [FirstName] [nvarchar](50) NULL,   
       [LastName] [nvarchar](50) NULL,   
       [JobTitle] [nvarchar](50) NULL,  
       [BusinessEntityType] [nvarchar](50) NULL  
   )  
   AS   
   -- Returns the first name, last name, job title and business entity type for the specified contact.  
   -- Since a contact can serve multiple roles, more than one row may be returned.  
   BEGIN  
   IF @PersonID IS NOT NULL   
   BEGIN  
        IF EXISTS(SELECT * FROM [HumanResources].[Employee] e   
        WHERE e.[BusinessEntityID] = @PersonID)   
        INSERT INTO @retContactInformation  
             SELECT @PersonID, p.FirstName, p.LastName, e.[JobTitle], 'Employee'  
             FROM [HumanResources].[Employee] AS e  
             INNER JOIN [Person].[Person] p ON p.[BusinessEntityID] = e.[BusinessEntityID]  
             WHERE e.[BusinessEntityID] = @PersonID;  
   
        IF EXISTS(SELECT * FROM [Purchasing].[Vendor] AS v  
        INNER JOIN [Person].[BusinessEntityContact] bec ON bec.[BusinessEntityID] = v.[BusinessEntityID]  
        WHERE bec.[PersonID] = @PersonID)  
        INSERT INTO @retContactInformation  
             SELECT @PersonID, p.FirstName, p.LastName, ct.[Name], 'Vendor Contact'   
             FROM [Purchasing].[Vendor] AS v  
             INNER JOIN [Person].[BusinessEntityContact] bec ON bec.[BusinessEntityID] = v.[BusinessEntityID]  
             INNER JOIN [Person].ContactType ct ON ct.[ContactTypeID] = bec.[ContactTypeID]  
             INNER JOIN [Person].[Person] p ON p.[BusinessEntityID] = bec.[PersonID]  
             WHERE bec.[PersonID] = @PersonID;  
   
        IF EXISTS(SELECT * FROM [Sales].[Store] AS s  
        INNER JOIN [Person].[BusinessEntityContact] bec ON bec.[BusinessEntityID] = s.[BusinessEntityID]  
        WHERE bec.[PersonID] = @PersonID)  
        INSERT INTO @retContactInformation  
             SELECT @PersonID, p.FirstName, p.LastName, ct.[Name], 'Store Contact'   
             FROM [Sales].[Store] AS s  
             INNER JOIN [Person].[BusinessEntityContact] bec ON bec.[BusinessEntityID] = s.[BusinessEntityID]  
             INNER JOIN [Person].ContactType ct ON ct.[ContactTypeID] = bec.[ContactTypeID]  
             INNER JOIN [Person].[Person] p ON p.[BusinessEntityID] = bec.[PersonID]  
             WHERE bec.[PersonID] = @PersonID;  
   
        IF EXISTS(SELECT * FROM [Person].[Person] AS p  
        INNER JOIN [Sales].[Customer] AS c ON c.[PersonID] = p.[BusinessEntityID]  
        WHERE p.[BusinessEntityID] = @PersonID AND c.[StoreID] IS NULL)   
        INSERT INTO @retContactInformation  
             SELECT @PersonID, p.FirstName, p.LastName, NULL, 'Consumer'   
             FROM [Person].[Person] AS p  
             INNER JOIN [Sales].[Customer] AS c ON c.[PersonID] = p.[BusinessEntityID]  
             WHERE p.[BusinessEntityID] = @PersonID AND c.[StoreID] IS NULL;   
        END  
   RETURN;  
   END;  
   

For more information, see ALTER FUNCTION (Transact-SQL).