UDF in sqlserver 7.0
-
Can anyone suggest how can I implement UDF (I know that this functionailty is not available ) in sqlserver 7.0.
I basically want to do it like this...
select tid, cost(tid) from test
cost(tid) is some stored procedure.
Right now, I am using cursor, and temptables .
Can any one suggest me how to achieve this without using them!
-
It all depends on the complexity of your "cost" function. If cost(tid) can be implemented as a computed column or a set-based subselect, then it's entirely possible that a view can be created where
[cost(tid)]
is one of the computed columns.
Then you could query like:
select tid, [cost(tid)] from vtest
Cheers,
- Mark
Cheers,
- Mark -
With the mother of all SELECTs and maybe a few CASE statements.
Need more info.
-
COST(TID) is a bunch of select statements from different tables based on the value of tid. I can use union of all those select statements and write a single query, but I do not want to use unions as it will take more time. that's why I split my cost stored procedure in to a bunch of select statements.
select @val1= val from table1 where tid=@tid
select @val2= val from table2 where tid=@tid
select @val3= val from table3 where tid=@tid
select @val4= val from table4 where tid=@tid
return @val1+@val2+@val3+@val4
-
You do have a covering index for all the tables, 1 to 4, for the column tid
How many records, in each table, 1 to 4
Assume the column [val] in all the tables are of the same type.
-
Given your SP code, a view and a subsequent select could be:
CREATE VIEW vTest
AS
SELECT *,
[cost(tid)] =
(select top 1 val from table1 where tid=Test.tid)
+ (select top 1 val from table2 where tid=Test.tid)
+ (select top 1 val from table3 where tid=Test.tid)
+ (select top 1 val from table4 where tid=Test.tid)
FROM Test
GO
select tid, [cost(tid)] from vtestCheers,
- Mark
Cheers,
- Mark -
Without more specifics it's difficult not to generalize.
The view option will work but could be inneficient if there are more than 4 tables and/or a lot of rows.
I would do one of two things
1. Create and use a temp table to contain the summary of the values by tid.
2. Left Join the 4 tables and do the sum eg
select tid, isnull(a.val,0)+isnull(b.val,0)+isnull(c.val,0)+isnull(d.val,0) as cost
from test t
left outer join table1 a on a.tid = t.tid
left outer join table2 b on b.tid = t.tid
left outer join table3 c on c.tid = t.tid
left outer join table4 d on d.tid = t.tid
Far away is close at hand in the images of elsewhere.
Anon. -
That is what exactly I was doing - getting the cost, and storing in temp table for each tid. But I want to avoid the use of temp table as this stored procedure will be accessed by multiple users at the same time . And temp table might degrade the performance as it locks tempdb.
my stored procedure basically looks like this
1. select tid, cost(tid) from test where tid in (@tid_list) --- I am storing tid, and corresponding cost in temp table (which I basically want to eliminate)
2. get tid based on cost(tid) value --- I use some condition for cost(tid)- like >,<, = etc. Match some payments to it
3. recalculate the cost for tid in @tid_list
4. repeat this process until cost is 0 for all tid in @tid_list
All my tables have nearly 200,000 rows.
so can you tell me which approach is best.
thanks
-
What I was trying to get at was to materialize the view in the end.
mccork got their first.
Second thought, maybe one should look at the whole problem statement, specially the apparent iterative nature, etc. and not only at partial solution.
Viewing 9 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply