Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2006

    Unanswered: Problem with Functions

    I have a main table that stores the names of all the tables in that database & the next pkey that the record in the respective table should have when a new record is inserted in it(This is the requirement). I am writing a function which returns the primary key of the tablename entered.

    The function that i have written is as follows:

    CREATE Function next_pkey(@in_tablename varchar(250),@in_increment int)
    RETURNS int
    DECLARE @nextid int
    DECLARE @out_nextid int
    SET @out_nextid = -1
    UPDATE main_ids SET nextid = nextid + @in_increment WHERE tablename = @in_tablename
    IF (@@ROWCOUNT = 0)
    INSERT INTO main_ids (tablename, nextid) VALUES (@in_tablename, 1 + @in_increment)
    SET @nextid = 1
    SELECT @nextid = nextid - @in_increment FROM main_ids WHERE tablename = @in_tablename
    IF (@@ERROR = 0)
    SET @out_nextid = @nextid
    Return @out_nextid

    This function gives 2 erros as mentioned below:
    Invalid use of 'UPDATE' within a function.
    Invalid use of 'INSERT' within a function.

    I have the following query:
    Is an update statement allowed in a function?

    Can you tell me what should i do so that this function executes properly???
    Plz Help?????

  2. #2
    Join Date
    Feb 2004
    Bangalore, India
    from BOL...

    The types of statements that are valid in a function include:

    DECLARE statements can be used to define data variables and cursors that are local to the function.

    Assignments of values to objects local to the function, such as using SET to assign values to scalar and table local variables.

    Cursor operations that reference local cursors that are declared, opened, closed, and deallocated in the function. FETCH statements that return data to the client are not allowed. Only FETCH statements that assign values to local variables using the INTO clause are allowed.

    Control-of-flow statements.

    SELECT statements containing select lists with expressions that assign values to variables that are local to the function.

    UPDATE, INSERT, and DELETE statements modifying table variables that are local to the function.

    EXECUTE statements calling an extended stored procedure.


  3. #3
    Join Date
    Nov 2002
    Quote Originally Posted by sweetypie
    Can you tell me what should i do so that this function executes properly???
    Turn it into a Procedure, making your return value an outbound parameter.

    Functions are not allowed to change data.

Posting Permissions

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