Monday, July 13, 2009

Invalid use of side-effecting or time-dependent operator in 'UPDATE' within a function.

I have run into a very interesting and very frustrating problem with Sql Server.
So far my experimenting points to a bug in sql server, though I wish it was something I was doing wrong.

I have created a scalar-valued function that uses a table variable. According to microsoft's msdn and several other sites doing inserts, updates, and deletes on local table variables in user functions is very valid.

In my case I was able to get Inserts and Deletes to work, but am having all sorts of issues with the Update statement. I copied microsofts example one and that executed fine, so then I started replacing pieces of their code with my own and saving/executing until it was a duplicate of my original function. At this point I assumed something must have been cached incorrectly in my original query window.

However, I needed to add a second update statement to the function and while the first one still works the second one won't take. So after messing with it for a bit I once again executed the microsoft example and bit by bit copied each piece of my sproc over replacing their code; and what do you know it worked again.

The only difference I can see is that their update statements are inside of a While loop, so it is still a bug in sql server, but I might be getting a little closer to a better work around.

Update: Looks like it might have been my problem after all, which is wonderful. I was using square brackets around my table variables; on a co-workers suggestion I removed those and my queries started working.

I'm guessing that all I was doing before was figuring out a way to bypass the query parser as can often happen in large stored procedures.


  1. I too ran in to the same problem, in a table valued function in SQL Server 2005. Your post helped me understand that it's a parser glitch. So I asked myself how can I "help" the parser understand that I'm updating a table variable, and not a regular table? I solved the problem by replacing the "UPDATE table_alias SET ... " statement with "UPDATE @table SET ... ".


    1. AnonymousMay 15, 2013

      Thanks, that helped.

    2. Thanks! That is an awesome tidbit on table variables.

  2. To the Anonymous above: thank you, you've saved me hours of tearing my hair out!

    Weirdly enough, using the table alias works perfectly in 2005 Express and 2008 Express, but I get the 'Invalid use...' error in 2005 Standard Edition!

  3. I get the error in 2008 R2... I would never have considered removing the alias but it does appear to work!

  4. Thanks a lot for help. I could get rid of this error by removing allias name. THanks a lot

  5. Aaron CarpenterMarch 20, 2012

    Yes, kudos to whomever posted the first comment. It did the trick! Thanks again!


Please leave your thoughts, I love hearing what you got out of the post. Spam comments will be removed.