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