Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Run function for every record in table

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.
Go to Top of Page

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-06-12 : 10:44:48
<<
Does "cross apply" exist in v2000?
>>

No it does not exist

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

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

Go to Top of Page

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.
Go to Top of Page

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.

- Jeff
http://weblogs.sqlteam.com/JeffS
Go to Top of Page

Nowy
Yak Posting Veteran

57 Posts

Posted - 2007-06-12 : 12:03:05
where can I find that article?
Go to Top of Page

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-06-12 : 12:14:06
here is the article link http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=84856


KH

Go to Top of Page

Nowy
Yak Posting Veteran

57 Posts

Posted - 2007-06-13 : 04:15:50
I've written this query

SELECT g.SampleDateTime, f.A, f.B, f.R2
FROM GC_G_FID_L as g
CROSS APPLY
dbo.Interpolate(g.C4, g.C5, g.C6, g.C7, g.C8, g.C9, g.C10, g.C11, g.C12) f
WHERE g.R2 IS NULL

But how can I update table "GC_G_FID_L" with the output of the above query? Where "g.R2" IS NULL?
Go to Top of Page

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-06-13 : 04:18:08
Are you using SQL 2000 or 2005 ?


KH

Go to Top of Page

Nowy
Yak Posting Veteran

57 Posts

Posted - 2007-06-13 : 04:19:16
SQL2005 in this case
Go to Top of Page

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 1980
blog: http://weblogs.sqlteam.com/mladenp
Go to Top of Page

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-06-13 : 04:57:33
[code]
SELECT g.SampleDateTime, f.A, f.B, f.R2
update g
set R2 = f.R2

FROM GC_G_FID_L as g
CROSS APPLY
dbo.Interpolate(g.C4, g.C5, g.C6, g.C7, g.C8, g.C9, g.C10, g.C11, g.C12) f
WHERE g.R2 IS NULL
[/code]


KH

Go to Top of Page

Nowy
Yak Posting Veteran

57 Posts

Posted - 2007-06-13 : 05:07:36
thnx
Go to Top of Page
   

- Advertisement -