修改用户定义函数

适用于: SQL Server Azure SQL 数据库 Azure SQL 托管实例

可以使用 SQL Server Management Studio 或 Transact-SQL 在 SQL Server 中修改用户定义函数。 按如下所述修改用户定义函数将不会更改函数的权限,并且也不会影响任何依赖的函数、存储过程或触发器。

限制和局限

ALTER FUNCTION 不能用于执行以下任何操作:

  • 将标量值函数更改为表值函数,反之亦然。

  • 将内联函数更改为多语句函数,反之亦然。

  • 将 Transact-SQL 函数更改为 CLR 函数,反之亦然。

权限

需要对函数或架构具有 ALTER 权限。 如果函数指定用户定义类型,则需要对该类型具有 EXECUTE 权限。

使用 SQL Server Management Studio

  1. 对于要修改的函数,选择包含该函数的数据库旁的加号。

  2. 选择“可编程性”文件夹旁的加号。

  3. 对于要修改的函数,选择包含该函数的文件夹旁的加号:

    • Table-valued Function

    • 标量值函数

    • Aggregate 函数

  4. 右键单击要修改的函数,然后单击“修改”

  5. 在查询窗口中,对 ALTER FUNCTION 语句进行必要的更改。

  6. 在“文件”菜单上,选择“保存”以保存 table_name

使用 Transact-SQL

  1. 在“对象资源管理器”中,连接到数据库引擎的实例。

  2. 在标准栏上,选择“新建查询” 。

  3. 将以下示例复制粘贴到查询窗口中,然后选择“执行”。

    下面的代码示例将更改一个标量值函数。

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

    下面的代码示例将更改一个表值函数。

    -- Table-Valued Function
    USE [AdventureWorks2022]
    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;
    

另请参阅