SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Run function for every record in table
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Nowy
Yak Posting Veteran

57 Posts

Posted - 06/12/2007 :  10:21:54  Show Profile  Reply with Quote
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?


Edited by - spirit1 on 06/13/2007 04:37:47

nr
SQLTeam MVY

United Kingdom
12543 Posts

Posted - 06/12/2007 :  10:28:57  Show Profile  Visit nr's Homepage  Reply with Quote
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

India
22755 Posts

Posted - 06/12/2007 :  10:44:48  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
<<
Does "cross apply" exist in v2000?
>>

No it does not exist

Madhivanan

Failing to plan is Planning to fail

Edited by - madhivanan on 06/12/2007 10:45:35
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17627 Posts

Posted - 06/12/2007 :  10:47:43  Show Profile  Reply with Quote
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

United Kingdom
12543 Posts

Posted - 06/12/2007 :  10:48:11  Show Profile  Visit nr's Homepage  Reply with Quote
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

USA
7423 Posts

Posted - 06/12/2007 :  11:47:08  Show Profile  Visit jsmith8858's Homepage  Reply with Quote
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 - 06/12/2007 :  12:03:05  Show Profile  Reply with Quote
where can I find that article?
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17627 Posts

Posted - 06/12/2007 :  12:14:06  Show Profile  Reply with Quote
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 - 06/13/2007 :  04:15:50  Show Profile  Reply with Quote
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?

Edited by - Nowy on 06/13/2007 04:16:56
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17627 Posts

Posted - 06/13/2007 :  04:18:08  Show Profile  Reply with Quote
Are you using SQL 2000 or 2005 ?


KH

Go to Top of Page

Nowy
Yak Posting Veteran

57 Posts

Posted - 06/13/2007 :  04:19:16  Show Profile  Reply with Quote
SQL2005 in this case
Go to Top of Page

spirit1
Cybernetic Yak Master

Slovenia
11751 Posts

Posted - 06/13/2007 :  04:37:27  Show Profile  Visit spirit1's Homepage  Reply with Quote
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

Edited by - spirit1 on 06/13/2007 04:38:00
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17627 Posts

Posted - 06/13/2007 :  04:57:33  Show Profile  Reply with Quote

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



KH

Go to Top of Page

Nowy
Yak Posting Veteran

57 Posts

Posted - 06/13/2007 :  05:07:36  Show Profile  Reply with Quote
thnx
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.14 seconds. Powered By: Snitz Forums 2000