I have a recursive scalar function that needs to update a record in another table based on the value it is returning, however UPDATE statements are not allowed in the function.
How can I update the table from within the function?
+3
A:
UPDATE statements are not allowed in the function
That's the rule - functions are not allowed to have any data-changing side-affects.
You have to use a Stored Procedure to UPDATE.
ck
2010-09-27 15:21:17
There is a way around this actually using `open rowset` but probably not advisable.
Martin Smith
2010-09-27 15:26:02
ok - I'm sure there are other workarounds since this must come up often. Right now I'm considering passing a table to the function, creating a copy of that table (because it will be readonly), and inserting a record that has values for an update. Then call the function with a SP that does an update for each record in the table. Seems unnecessary... are there other options here? (I might move this to a new question)
jcoon
2010-09-27 15:44:30
@jcoon - yeah, it may be worthwhile posting what you are actually trying to do...
ck
2010-09-27 15:46:08
follow-up: http://stackoverflow.com/questions/3805547/how-to-use-a-recursive-function-to-update-a-table
jcoon
2010-09-27 15:57:25