Loop Through Table Columns Into Stored Procedure 2

[larrydavid]

Hello,

I have a SQL Server 2005 stored procedure which has been created to return results based on the address passed in, which works for an individual address:

EXEC dbo.udsp_Geocode_Locations 
@Address = '1234 N. Main Street', 
@City = 'Santa Ana', 
@State = 'CA'

What I'm trying to do is loop through the Address, City and State columns in an address table and pass in those values for each address ao I can get all the results.

Any help greatly appreciated.

Thanks,
Larry

 

[gmmastros]

You have a couple of choices.

You could write a cursor to loop through the address table.
You could write a while loop to go through the address table.
You could (possibly) rewrite the procedure in to a function and then use cross apply to run it for each row.

It would be easier to advise you if you would post the contents of the stored procedure.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[larrydavid]

Hi George,

Here is the stored procedure:

CREATE PROCEDURE dbo.udsp_Geocode_Locations 
@Address varchar(80) = NULL OUTPUT,
@City varchar(40) = NULL OUTPUT,
@State varchar(40) = NULL OUTPUT,
@Country varchar(40) = NULL OUTPUT,
@PostalCode varchar(20) = NULL OUTPUT,

@County varchar(40) = NULL OUTPUT,

@GPSLatitude numeric(9,6) = NULL OUTPUT,
@GPSLongitude numeric(9,6) = NULL OUTPUT,
@MapURL varchar(1024) = NULL OUTPUT

AS
BEGIN
 SET NOCOUNT ON

DECLARE @URL varchar(MAX)
 SET @URL = '[URL unfurl="true"]http://maps.google.com/maps/api/geocode/xml?sensor=false&address='[/URL] +
 CASE WHEN @Address IS NOT NULL THEN @Address ELSE ' END +
 CASE WHEN @City IS NOT NULL THEN ', ' + @City ELSE ' END +
 CASE WHEN @State IS NOT NULL THEN ', ' + @State ELSE ' END +
 CASE WHEN @PostalCode IS NOT NULL THEN ', ' + @PostalCode ELSE ' END +
 CASE WHEN @Country IS NOT NULL THEN ', ' + @Country ELSE ' END
 SET @URL = REPLACE(@URL, ' ', '+')

 DECLARE @Response varchar(8000)
 DECLARE @XML xml
 DECLARE @Obj int 
 DECLARE @Result int 
 DECLARE @HTTPStatus int 
 DECLARE @ErrorMsg varchar(MAX)

EXEC @Result = sp_OACreate 'MSXML2.ServerXMLHttp', @Obj OUT 

 BEGIN TRY
 EXEC @Result = sp_OAMethod @Obj, 'open', NULL, 'GET', @URL, false
 EXEC @Result = sp_OAMethod @Obj, 'setRequestHeader', NULL, 'Content-Type', 'application/x-[URL unfurl="true"]www-form-urlencoded'[/URL]
 EXEC @Result = sp_OAMethod @Obj, send, NULL, '
 EXEC @Result = sp_OAGetProperty @Obj, 'status', @HTTPStatus OUT 
 EXEC @Result = sp_OAGetProperty @Obj, 'responseXML.xml', @Response OUT 
 END TRY
 BEGIN CATCH
 SET @ErrorMsg = ERROR_MESSAGE()
 END CATCH

 EXEC @Result = sp_OADestroy @Obj

IF (@ErrorMsg IS NOT NULL) OR (@HTTPStatus <> 200) BEGIN
 SET @ErrorMsg = 'Error in spGeocode: ' + ISNULL(@ErrorMsg, 'HTTP result is: ' + CAST(@HTTPStatus AS varchar(10)))
 RAISERROR(@ErrorMsg, 16, 1, @HTTPStatus)
 RETURN 
 END

SET @XML = CAST(@Response AS XML)

 SET @GPSLatitude = @XML.value('(/GeocodeResponse/result/geometry/location/lat) [1]', 'numeric(9,6)')
 SET @GPSLongitude = @XML.value('(/GeocodeResponse/result/geometry/location/lng) [1]', 'numeric(9,6)')

SET @City = @XML.value('(/GeocodeResponse/result/address_component[type="locality"]/long_name) [1]', 'varchar(40)') 
 SET @State = @XML.value('(/GeocodeResponse/result/address_component[type="administrative_area_level_1"]/short_name) [1]', 'varchar(40)') 
 SET @PostalCode = @XML.value('(/GeocodeResponse/result/address_component[type="postal_code"]/long_name) [1]', 'varchar(20)') 
 SET @Country = @XML.value('(/GeocodeResponse/result/address_component[type="country"]/short_name) [1]', 'varchar(40)') 
 SET @County = @XML.value('(/GeocodeResponse/result/address_component[type="administrative_area_level_2"]/short_name) [1]', 'varchar(40)') 

 SET @Address = 
 ISNULL(@XML.value('(/GeocodeResponse/result/address_component[type="street_number"]/long_name) [1]', 'varchar(40)'), '???') + ' ' +
 ISNULL(@XML.value('(/GeocodeResponse/result/address_component[type="route"]/long_name) [1]', 'varchar(40)'), '???') 
 SET @MapURL = '[URL unfurl="true"]http://maps.google.com/maps?f=q&hl=en&q='[/URL] + CAST(@GPSLatitude AS varchar(20)) + '+' + CAST(@GPSLongitude AS varchar(20))


 SELECT 
 @GPSLatitude AS GPSLatitude,
 @GPSLongitude AS GPSLongitude,
 @City AS City,
 @State AS [State],
 @PostalCode AS PostalCode,
 @Address AS [Address],
 @County AS County,
 @MapURL AS MapURL,
 @XML AS XMLResults

END

Thanks,
Larry

 

[larrydavid]

Hi George,

Also, what I was trying to do is eventually update another table with the results from this one. Just wondering, but is it possible to do it all in one cursor or while loop?

Thanks,
Larry

 

[markros]

Do you need to go through this complex code? May be it's easier to get the post office databases instead?

Anyway, if this is, say, one time job, you need to use a cursor.

Check Brad Schulz blog showing how to use cursors effectively
The Truth about Cursors - Part 1

PluralSight Learning Library

 

[larrydavid]

Hi Mark,

Thanks, that's a great link.

Larry

 

[larrydavid]

Hi All,

OK, I have this select cursor working with the stored proc as displayed above:

declare @address1 varchar(50), 
		@city varchar(50), 
		@state varchar(2)
declare cur cursor 
for select address1, city, state
from Profile_Metrics
open cur
fetch next from cur into @address1, @city, @state
while @@fetch_status = 0
begin
exec dbo.udsp_Geocode_Locations @address1, @city, @state
fetch next from cur into @address1, @city, @state
end
close cur
deallocate cur

Now I just need to update the table columns [lat] and [long] with the output from my cursor script above. Here is row 1 of 50 from the table output by cursor:

GPS_Lat     GPS_Long      Address1      City          State
34.187418   -118.398657   341 Main St.  Los Angeles   CA

So close, just need to be able to update my Profile_Metrics
table (lat and long columns) with the Lat and Long output.

Thanks,
Larry

 

[larrydavid]

Got it figured out. I just did the update in the first stored procedure and run the cursor script to execute the stored procedure for each address. However, the address data needs to be clean, valid data to begin with, otherwise the GoogleMapsAPI geolocation service returns a null value.

Thanks for the point in the right direction.

Larry