Author |
Topic |
Nowy
Yak Posting Veteran
57 Posts |
Posted - 2007-06-12 : 10:21:54
|
First:- I've written an function "MyFirstFunction"- I've an table "Test" with 8 columns ( this have about 3000 records )The function needs 8 params from the 8 columns from the table. And output 2 values in the table "Test2"How is it possible to run the function for every 3000 records in the "Test" table? |
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2007-06-12 : 10:28:57
|
Does "cross apply" exist in v2000? Have a look in bol. If it does it will execute the function for each row.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2007-06-12 : 10:44:48
|
<<Does "cross apply" exist in v2000? >>No it does not existMadhivananFailing to plan is Planning to fail |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2007-06-12 : 10:47:43
|
quote: How is it possible to run the function for every 3000 records in the "Test" table?
You can't if you are using SQL Server 2000. With 2005, you can use the new CROSS APPLY.Why not using Stored Procedure to do it ? KH |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2007-06-12 : 10:48:11
|
Sounds a bit like you have a table valued function which you can't run for each row in the table.You can split it into two functions each returning one of the values or concatenate the values.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2007-06-12 : 11:47:08
|
Nowy -- how about some specific examples about what you are trying to do? There may be an easier way to get what you need without the need for a UDF.As mentioned, SQL 2005 provides this ability via CROSS APPLY -- in fact, my latest article posted just today covers exactly what you need, but it is not supported in SQL 2000.- Jeffhttp://weblogs.sqlteam.com/JeffS |
|
|
Nowy
Yak Posting Veteran
57 Posts |
Posted - 2007-06-12 : 12:03:05
|
where can I find that article? |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
|
Nowy
Yak Posting Veteran
57 Posts |
Posted - 2007-06-13 : 04:15:50
|
I've written this querySELECT g.SampleDateTime, f.A, f.B, f.R2FROM GC_G_FID_L as gCROSS APPLYdbo.Interpolate(g.C4, g.C5, g.C6, g.C7, g.C8, g.C9, g.C10, g.C11, g.C12) fWHERE g.R2 IS NULLBut how can I update table "GC_G_FID_L" with the output of the above query? Where "g.R2" IS NULL? |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2007-06-13 : 04:18:08
|
Are you using SQL 2000 or 2005 ? KH |
|
|
Nowy
Yak Posting Veteran
57 Posts |
Posted - 2007-06-13 : 04:19:16
|
SQL2005 in this case |
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2007-06-13 : 04:37:27
|
since you're using sql server 2005 this should go in the 2005 forum.Moved from SQL Server Development (2000)_______________________________________________Causing trouble since 1980blog: http://weblogs.sqlteam.com/mladenp |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2007-06-13 : 04:57:33
|
[code]SELECT g.SampleDateTime, f.A, f.B, f.R2update gset R2 = f.R2FROM GC_G_FID_L as gCROSS APPLYdbo.Interpolate(g.C4, g.C5, g.C6, g.C7, g.C8, g.C9, g.C10, g.C11, g.C12) fWHERE g.R2 IS NULL[/code] KH |
|
|
Nowy
Yak Posting Veteran
57 Posts |
Posted - 2007-06-13 : 05:07:36
|
thnx |
|
|
|