Invalid use of INSERT within a function.

Is it possible to insert into a table from a function. I have a function that searches for a unique group id, and if the group does not exist, it inserts and returns the id. I created a procedure with the same code (except for the returns) and it operated properly, so it shouldn't be a logic issue. I cannot find any resources that say inserting from within a function is disallowed. Microsoft even does it on one of their sql server 2000 examples in msdn.

Any help would be greatly appreciated.You cannot INSERT, UPDATE, or DELETE from tables in a FUNCTION. The examples you probably saw did so to locally defined variables of type TABLE.

You can return a value from a stored procedure to get the new ID. You can do this with an output parameter or with a RETURN in the stored procedure. To get the value of the RETURN, execute the stored procedure like this:

DECLARE @.ResultCode int
EXEC @.ResultCode = uspGetMyGroup

Originally posted by saderax
Is it possible to insert into a table from a function. I have a function that searches for a unique group id, and if the group does not exist, it inserts and returns the id. I created a procedure with the same code (except for the returns) and it operated properly, so it shouldn't be a logic issue. I cannot find any resources that say inserting from within a function is disallowed. Microsoft even does it on one of their sql server 2000 examples in msdn.

Any help would be greatly appreciated.