修改使用者定義函式

發行項
07/12/2024

適用於： 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

選取包含要修改之函式的資料庫旁邊的加號。
選取 [可程式性] 資料夾旁的加號。
選取包含要修改之函式的資料夾旁邊的加號：
- 資料表值函式
- 純量值函式
- 彙總函式
以滑鼠右鍵按一下您要修改的函數，然後選取 [修改]。
在查詢視窗中，對 ALTER FUNCTION 陳述式進行必要的變更。
在 [檔案] 功能表上，選取 [儲存 function_name]。

使用 Transact-SQL

在物件總管中，連線至資料庫引擎的執行個體。
在標準列上，選取 [新增查詢] 。

複製下列範例並將其貼到查詢視窗中，然後選取 [執行]。

下列程式碼範例會改變純量值函式。

-- 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;

另請參閱

ALTER FUNCTION (Transact-SQL)

共用方式為