SQL Server Error Messages - Msg 443 - UPDATE

Error Message

Server: Msg 443, Level 16, State 2, Line 1
Invalid use of 'UPDATE' within a function.

Causes:

As the message suggests, you are not allowed to use the UPDATE statement inside a function unless the UPDATE statement is directed to a table variable local to the function.

To illustrate, here’s an example of a function that will generate the error:

CREATE FUNCTION [dbo].[ufn_GetTotalAmount]
( @OrderID    INT,
  @Amount     MONEY )
RETURNS MONEY
AS
BEGIN

DECLARE @TotalAmount    MONEY

SELECT @TotalAmount = [TotalAmount] + @Amount
FROM [dbo].[Orders]
WHERE [OrderID] = @OrderID

UPDATE [dbo].[Orders]
SET [TotalAmount] = @TotalAmount
WHERE [OrderID] = @OrderID

RETURN @TotalAmount

END

Since the UPDATE statement is directed to a table and not to a table variable local to the function, the following error is encountered:

Server: Msg 443, Level 16, State 1, Procedure ufn_GetTotalAmount, Line 14
Invalid use of 'UPDATE' within a function.

Solution / Work Around:

To avoid this error from happening, make sure that you don’t use an UPDATE statement inside a user-defined function unless it’s updating a local table variable. If you really need to use the UPDATE statement on a table, you have to use a stored procedure instead of a user-defined function for this purpose.

Here’s how the script shown above will look like using a stored procedure:

CREATE PROCEDURE [dbo].[usp_GetTotalAmount]
  @OrderID      INT,
  @Amount       MONEY,
  @TotalAmount  MONEY OUTPUT
AS

SELECT @TotalAmount = [TotalAmount] + @Amount
FROM [dbo].[Orders]
WHERE [OrderID] = @OrderID

UPDATE [dbo].[Orders]
SET [TotalAmount] = @TotalAmount
WHERE [OrderID] = @OrderID

Since a stored procedure can only return an integer value, the total amount needs to be returned as an output parameter because it is of money data type.