2

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?

A-K
  • 16,804
  • 8
  • 54
  • 74
Jason Coon
  • 17,601
  • 10
  • 42
  • 50

1 Answers1

3

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.

cjk
  • 45,739
  • 9
  • 81
  • 112
  • There is a way around this actually using `open rowset` but probably not advisable. – Martin Smith Sep 27 '10 at 15:26
  • 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) – Jason Coon Sep 27 '10 at 15:44
  • @jcoon - yeah, it may be worthwhile posting what you are actually trying to do... – cjk Sep 27 '10 at 15:46
  • follow-up: http://stackoverflow.com/questions/3805547/how-to-use-a-recursive-function-to-update-a-table – Jason Coon Sep 27 '10 at 15:57