SQL Server Performance

How do I use an UPDATE statement in a function?

Discussion in 'SQL Server 2005 General Developer Questions' started by kingofSQL, Nov 26, 2008.

  1. kingofSQL New Member

    Any suggestions?
    I'm getting an error when trying to use an UPDATE statement inside of a function.
    error: Invalid use of side-effecting or time-dependent operator in 'UPDATE' within a function.
  2. satya Moderator

  3. Madhivanan Moderator

    [quote user="kingofSQL"]
    Any suggestions?
    I'm getting an error when trying to use an UPDATE statement inside of a function.
    error: Invalid use of side-effecting or time-dependent operator in 'UPDATE' within a function.
    [/quote]
    Why dont you make it as a procedure instead of function?
  4. FrankKalis Moderator

    Can you please post your code?
  5. EasuBoy New Member

    I am getting the same error as well.
    Here are the code for the same.

    I would like to convert one procedure to function and call that function into select statement.

    Procedure listed as below:
    CREATE PROCEDURE Test_NextEmployee
    AS
    SET NOCOUNT ON
    DECLARE @iReturn int
    BEGIN TRANSACTION
    SELECT @iReturn = Sequence FROM .dbo.EMPSEQUENCE (TABLOCKX) -- set exclusive table lock
    UPDATE dbo.EMPSEQUENCE SET Sequence = ( Sequence + 1 )
    COMMIT TRANSACTION
    SELECT @iReturn
    RETURN @iReturn

    And after that i have to call that function into below Insert Statement in place of AutoKey column.

    Insert Into EmpSequence (KeySegment, Keystn, KeySegmentType, FinlSegment, Status)
    select 'Auto key', eop.Keystn, 0, a.FinlSegment, 2
    from KeyEOP KP
    join KeyEOPSegmentDetail a on a.KeyEOP=kP.KeyEOP and a.updop < 2
    join Kfinl f on eop.keyeop = f.keyfeop
    join FinlSegmentDetail ff on f.keyfinl = ff.keyfinl and ff.updop < 2
    and ff.finlsegment = a.finlsegment
    join stn I on i.Keystn=EOP.Keystn and Class in ( 8,15)
    where EOP.UpdOp<>2
    Can anyone please help with that?
    Thanks

Share This Page