Roberto Peña writes "Hi,I have a bit of a problem, I'm using a set of user defined funtions to process several hundreds/thousands of records; each function works based on the output of another one.Now, my problem, I want to "emulate computed colums"...I have items that have records based on a timestamp (date and time, not the datatype) and there are several items with many many records associated, for example:item A:01-jan-2005 10:30, 1501-jan-2005 12:50, 1801-jan-2005 16:25, 1401-jan-2005 16:30, 20...item B:01-jan-2005 10:30, 5001-jan-2005 12:50, 6001-jan-2005 16:26, 4801-jan-2005 16:31, 73...
(Theres an Items table and a Details table)I want to be able to allow my users to "create virtual items" lets say Item C = Item B - Item A, Item D = 3 * Item A / Item B; this requires use of joins and dynamic sql and I need to be able to create those items on the fly.I know this:1- Functions do not allow usage of dynamic sql2- In order to use the output from an Stored Procedure within another I must create a temp table3- I cant call a stored procedure from a function4- My users dont have the skills to come up with the sql on their ownso far I have come up with a system where if I need to access the "computed" data I call a stored procedure and pupulate a temp table if not I just call the appropiate function; but that means I can no longer use my functions chainis there any other way to do this?Why do I have this setup? because I need to access the data, do different calculations and perform agregate functions at different stages. I think OLAP would be no help here as I add 5000 to 20000 records daily (a single batch) and that is for several identical databases but with diferent contents, most of the time we want to see fresh loaded data.one of those database has already 18,839,999 item recors and I have to wait 4 more month to create a historical database and cut that in half"
SP, UDF and Execute 
Posted - 2005-03-18 : 08:05:21