Stored Procedure vs Function - Calculations
-
Venturing off into a new area... I have written a stored procedure which retreives a recordset based upon a date value and a nvarchar value. That part works, however, I am stumped if you can use a stored procedure in a form statement.
What I am trying to accomplish (since we do not have an OLAP environment) is to compile a table with the needed information, and possible load it into a temp table to run multiple calculations on it (such as Max, AVG, and calculations based upon the MAX, AVG results) [Multiple SELECT statments against the temp table]. I am not sure if I went the wrong router with the USP and should have created FN's.
Looking for some suggestions, and feedback on how to load the SP or FN into a temp table.
"Life without progression is entropy"
Sam Jaynes -
Is this really a "temp" table ? How persistent do you really need it to be ? Just for the lifespan of the stored procedure execution ? Or do you want to build it, and then have multiple subsequent processes use it ?
-
The Stored Procedure is used to format the data from multiple tables based upon the search criteria (hence not using a view) and produce meaningful data that will need to be use in future calculations. For example, say I wanted the data from May 2005, I would call the stored procedure which would get the data in the prescribed format. The next task which I am stumped on finding is... Finding the MAX(volume) value, the monthly average(volume); THEN using these two values to calculate a percentage of the monthly volume in a seperate select statement.
"Life without progression is entropy"
Sam Jaynes -
Hi,
I tend to use table valued UDFs for this purpose. You can use these in a JOIN statement as though they were tables. The gotcha, is that unlike inline UDFs in the SELECT clause, the parameters can't be column names from the other tables, they have to be literals, and you can't supply GetDate() as a date parameter
. I only use stored procedures if I am updating data.David
If it ain't broke, don't fix it...
-
I usually use functions to return one single value, to help make query's easier to write. Like for example say you have a departments table, with a recursive fk relationship. I wrote a function that can take a departmentId as it's argument and return the departmentId of it's root (top level) department. It made many, many queries much easier to write. They are also just a nice way to reuse and centralize code.
Stored procedures are what I use to abstract applications from the details of the tables, table names, attribute names, queries, and everything else. So instead of an application hard coding some select statement, it calls a stored procedure that returns the same data rows. That way I can get away with changes under the hood without crashing applications.
I would guess you likely just make functions for each derived value you want, and those functions could call the stored proc you wrote to get the same dataset to derive the AVG, MAX or whatever of.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply