locked
Why is my function throws this error? RRS feed

  • Question

  • Hi friend,
    I have the following fucntion:
    ----------------------------------------------------------------------------------------
    if exists(select 1 from sys.sysobjects where type = 'FR' and name = 'FnGenerateAnyCode')
    begin
    	drop function FnGenerateAnyCode
    end
    go
    
    create function FnGenerateAnyCode(@tableName varchar(100)) 
    returns char(6)
    as
    begin
    
    	declare @incre int;
    	declare @currentValue int;
    	declare @startValue int;
    	declare @CodeFor varchar(100);
    
    	if exists (select 1 from sys.sysobjects where type = 'U' and name = @tableName )
    	begin 
    		select 
    		@incre = Increment, 
    		@currentValue = CurrentValue from dbo.tblCodeGenerator 
    		where CodeFor = @tableName	
    
    		set @currentValue = @currentValue + @incre
    
    		update dbo.tblCodeGenerator 
    		set CurrentValue = @currentValue
    		where CodeFor = @tableName			
    		
    	end
    	else
    	begin
    		set @incre = 1; 
    		set @startValue = 1;
    		set @currentValue = 1;
    		set @CodeFor = @tableName
    
    		insert into tblCodeGenerator 
    		values (@CodeFor, @startValue, @incre, @currentValue)
    	end
    
    		return @CurrentValue
    end
    go

    when i run this i get this error:

    Msg 443, Level 16, State 15, Procedure FnGenerateAnyCode, Line 21
    Invalid use of side-effecting or time-dependent operator in 'UPDATE' within a function.
    Msg 443, Level 16, State 15, Procedure FnGenerateAnyCode, Line 33
    Invalid use of side-effecting or time-dependent operator in 'INSERT' within a function.
    

    why do i get this error and how do i fix this?

    thanks

    Friday, May 28, 2010 11:56 AM

Answers

  • Change it in to a stored procedure :)


    George
    blog | twitter
    • Proposed as answer by Naomi N Friday, May 28, 2010 12:02 PM
    • Marked as answer by code_warrior Saturday, May 29, 2010 3:32 AM
    Friday, May 28, 2010 12:01 PM
    Answerer
  • You're getting this error because data manipulation operations are not allowed in UDFs and it's well documented in BOL. As George said, the only way would be to switch to stored procedure.
    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    • Marked as answer by code_warrior Saturday, May 29, 2010 3:32 AM
    Friday, May 28, 2010 12:03 PM
  • You cannot have Insert/Update statement within this function as the table is not local to it. Please look at the link below.

    http://msdn.microsoft.com/en-us/library/ms191320(SQL.90).aspx

     

    • Marked as answer by code_warrior Saturday, May 29, 2010 3:32 AM
    Friday, May 28, 2010 12:07 PM

All replies

  • Change it in to a stored procedure :)


    George
    blog | twitter
    • Proposed as answer by Naomi N Friday, May 28, 2010 12:02 PM
    • Marked as answer by code_warrior Saturday, May 29, 2010 3:32 AM
    Friday, May 28, 2010 12:01 PM
    Answerer
  • You're getting this error because data manipulation operations are not allowed in UDFs and it's well documented in BOL. As George said, the only way would be to switch to stored procedure.
    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    • Marked as answer by code_warrior Saturday, May 29, 2010 3:32 AM
    Friday, May 28, 2010 12:03 PM
  • You cannot have Insert/Update statement within this function as the table is not local to it. Please look at the link below.

    http://msdn.microsoft.com/en-us/library/ms191320(SQL.90).aspx

     

    • Marked as answer by code_warrior Saturday, May 29, 2010 3:32 AM
    Friday, May 28, 2010 12:07 PM