ModifyUser-defined Functions

You can modify user-defined functions in SQL Server 2012 by using SQL Server Management Studio or Transact-SQL. 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.

[Top]

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 Save function_name.

[Top]

Using Transact-SQL

To modify a user-defined function

1. In Object Explorer, connect to an instance of Database Engine.

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

[Top]