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.

7 comments:

Anonymous said...

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

Thanks!

Anonymous said...

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!

Anonymous said...

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

Anonymous said...

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

Aaron Carpenter said...

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

Anonymous said...

Thanks, that helped.

Anonymous said...

Thanks! That is an awesome tidbit on table variables.