Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations gkittelson on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Using Calculations Stored in a Table in Stored Procedures

Status
Not open for further replies.

jjohns09

Technical User
Jun 26, 2001
15
US
I would like to store my calculations within a table in SQL and then use them in a stored procedure. Is this possible? The calculation table would consist of several fields; CalculationID, Name, and Calculation. Data might look something like the following:

CalculationID: 1
Name: AVAILABILITY
Calculation: ((DATA.AVAIL_EXPECTED * APPLICATIONS.REGION_COUNT) -(DATA.OUTAGE_MINUTES * DATA.AFFECTED_REGIONS))/(DATA.AVAIL_EXPECTED * APPLICATIONS.REGION_COUNT)

The reason that I ask is that my stored procedures are quite complex and the calculations seem to change every day. Remembering or even finding every occurence of a reference to a particular calculation is somewhat tedious and problematic.

Any help would be greatly appreciated.

Jeff
 
I really don't think this sounds like a very workable solution.

In your SP you would have to read the calculation into a string variable, then build the actual query into another string (concatenating the calculation where you want it) and then execute it using dynamic SQL (EXEC).

a) Performance is not going to be as good as you're using dynamic SQL so it can't reuse the execution plans.

b) If your query is complicated now, this method is going to make it even more so!

A much better solution would be to build these calcs into a user-defined function.

--James
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top