SQL Server Error Messages - Msg 443 - INSERT

Error Message

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

Causes:

This error is encountered when you use the INSERT command inside a user-defined function and the target table is not a table variable local to the function. The INSERT command is only allowed when modifying table variables local to the function.

To illustrate, here’s a user-defined function that will generate the error:

CREATE FUNCTION [dbo].[CreateNewRow] 
( @pFirstName VARCHAR(50), @pLastName VARCHAR(50) )
RETURNS INT
AS
BEGIN

INSERT INTO [dbo].[Student] ( [FirstName], [LastName] )
VALUES ( @pFirstName, @pLastName )

RETURN SCOPE_IDENTITY()
END
GO

Server: Msg 443, Level 16, State 2, Procedure CreateNewRow, Line 7
Invalid use of 'INSERT' within a function.

Here’s a user-defined function that uses the INSERT command but the modified table is a table variable local to the function:

CREATE FUNCTION [dbo].[ufn_GetMaxValue]
( @pInt1 INT, @pInt2 INT, @pInt3 INT, @pInt4 INT )
RETURNS INT
AS
BEGIN
DECLARE @IntTable TABLE ( [IntValue] INT )
DECLARE @MaxValue INT

INSERT INTO @IntTable ( [IntValue] ) VALUES ( @pInt1 )
INSERT INTO @IntTable ( [IntValue] ) VALUES ( @pInt2 )
INSERT INTO @IntTable ( [IntValue] ) VALUES ( @pInt3 )
INSERT INTO @IntTable ( [IntValue] ) VALUES ( @pInt4 )

SELECT @MaxValue = MAX( [IntValue] )
FROM @IntTable

RETURN @MaxValue
END
GO

This user-defined function also uses the INSERT command but since it is inserting into a local variable, then the error is not encountered.

Solution / Work Around:

Since the INSERT command is not allowed in a user-defined function, you have to use a stored procedure for this purpose instead. Here’s the same script as the above function but using a stored procedure:

CREATE PROCEDURE [dbo].[CreateNewRow]
    @pFirstName VARCHAR(50), 
    @pLastName VARCHAR(50)
AS

INSERT INTO [dbo].[Student] ( [FirstName], [LastName] )
VALUES ( @pFirstName, @pLastName )

RETURN SCOPE_IDENTITY()
GO