looping through a recordset in a SP

[Michael Imhoff]

What I have to do is loop through a table with about 900 records, do computations on each record and update a database, can someone help me out.

here is the code that I am using right now on another site with coldfusion but i want to convert it all to a stored proc for speed reasons.

<!--- Query the Stores Table --->
<cfquery name=&#34;Zip2&#34; datasource=&#34;#application.data#&#34; username=&#34;#application.username#&#34; password=&#34;#application.password#&#34;>
exec Stores_GetStoreZipInfo
</cfquery>

<!--- Loop through the STORE table --->
<cfloop query=&#34;zip2&#34;>
<cfset Lat1 = #zip1.lat#>
<cfset Lg1 = #zip1.long#>
<cfset Lat2 = #zip2.lat#>
<cfset Lg2 = #zip2.long#>

<!--- Do the actual distance calculation between the user&#39;s zipcode each store&#39;s zipcode --->

<cfset DistLat = 69.1 * (Lat2-Lat1)>
<cfset DistLong = 69.1 * (Lg2-Lg1) * #cos(Lat1 / 57.3)#>
<cfset Dist = ((#abs(DistLat)# * 2) + (#abs(DistLong)# * 2)) * 0.5>

<!--- Update the DISTANCE field on STORE table --->
<cfquery name=&#34;UpdateZips&#34; datasource=&#34;#application.data#&#34; username=&#34;#application.username#&#34; password=&#34;#application.password#&#34;>
exec Stores_UpdateZipSeachInfo &#39;#Dist#&#39;, &#39;#zip2.zipcode#&#39;
</cfquery>
</cfloop>

[Brian Knight]

Michael,

You&#39;ll want to do record level looping via a cursor. If you want an example of this try downloading ftp://downloads.swynk.com/sp_axeusers.SQL . If you need help above this, please email me.

Brian


------------
Michael Imhoff at 8/31/99 12:49:50 AM

What I have to do is loop through a table with about 900 records, do computations on each record and update a database, can someone help me out.

here is the code that I am using right now on another site with coldfusion but i want to convert it all to a stored proc for speed reasons.

<!--- Query the Stores Table --->
<cfquery name=&#34;Zip2&#34; datasource=&#34;#application.data#&#34; username=&#34;#application.username#&#34; password=&#34;#application.password#&#34;>
exec Stores_GetStoreZipInfo
</cfquery>

<!--- Loop through the STORE table --->
<cfloop query=&#34;zip2&#34;>
<cfset Lat1 = #zip1.lat#>
<cfset Lg1 = #zip1.long#>
<cfset Lat2 = #zip2.lat#>
<cfset Lg2 = #zip2.long#>

<!--- Do the actual distance calculation between the user&#39;s zipcode each store&#39;s zipcode --->

<cfset DistLat = 69.1 * (Lat2-Lat1)>
<cfset DistLong = 69.1 * (Lg2-Lg1) * #cos(Lat1 / 57.3)#>
<cfset Dist = ((#abs(DistLat)# * 2) + (#abs(DistLong)# * 2)) * 0.5>

<!--- Update the DISTANCE field on STORE table --->
<cfquery name=&#34;UpdateZips&#34; datasource=&#34;#application.data#&#34; username=&#34;#application.username#&#34; password=&#34;#application.password#&#34;>
exec Stores_UpdateZipSeachInfo &#39;#Dist#&#39;, &#39;#zip2.zipcode#&#39;
</cfquery>
</cfloop>