修改使用者定義函式
適用於: 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;