Results 1 to 2 of 2
  1. #1
    Join Date
    Sep 2002
    Posts
    1

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

  2. #2
    Join Date
    Sep 2002
    Location
    Atlanta, GA USA
    Posts
    19

    Arrow Re: Invalid use of 'INSERT' within a function.

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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •