1

I have a table of Facilities, and a table of Transmissions. I want to compute various things like, facility X sent 10124321409 transmissions in april 2011, of which 9959 were missing SSNs. These are huge tables, so I will store these computed values in a 'cache' table so that they can be accessed easily when a user looks over our system with a UI (ex. selects a date range). I have a bunch of user-defined functions that take a facility name and a date range and compute a value, like this

 set @s = dbo.getPctMissingSSNs(facilityX, 1/1/2012, 1/15/2012) //returns pct missing ssn from 1/1/12 to 1/15/12 at facilityX

From stackoverflow, I have learned that

  1. It is a bad idea to loop over a table's rows What is the best way to loop over a table's rows?
  2. It is better to do these sorts of calculations with stored procedures, rather than calling the data from the server to a client, calculating the values, and then uploading the output back to the server's cache.

Is there a way that I can update the cache for each of the facilities, for a bunch of date ranges, without (1) looping over rows in T-SQL (bad) or (2) looping over rows in VB.net to download the value from the server and then upload it right back again (also bad)

Community
  • 1
  • 1
bernie2436
  • 22,841
  • 49
  • 151
  • 244
  • If that is a scalar UDF, can't you just call the UDF as part of the select, for any insert/update? – Marc Gravell Apr 13 '12 at 20:12
  • it is a scalar udf. the facility name is a row in the table. i am using that name as a parameter. i want to do something like, for each fac in table update cache to udf(fac). But that seems like it requires looping thru the rows – bernie2436 Apr 13 '12 at 20:17
  • 2
    you shouldn't need to loop for that. I'm on mobile so hard to do a good example, but that sounds just like an upsert (`MERGE` in TSQL) and join. – Marc Gravell Apr 13 '12 at 20:21

1 Answers1

1

Use an update query. T-SQL will interpret the name of the field as the input parameter. So if you have a table with two columns: a facilityName and a cacheValue field, you can do something like this to update the cacheValue field so that it equals scalarUserDefinedFunction(facilityName)

Update cacheTable set cacheValue=scalarUserDefinedFunction(cacheTable.FacilityName)
bernie2436
  • 22,841
  • 49
  • 151
  • 244