Export (0) Print
Expand All

Modify User-defined Functions

You can modify user-defined functions in SQL Server 2014 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

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.

Arrow icon used with Back to Top link [Top]

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.

Arrow icon used with Back to Top link [Top]

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

Arrow icon used with Back to Top link [Top]

Community Additions

ADD
Show:
© 2014 Microsoft