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