This geocoding web service can only process a single request at a time, so to process multiple rows you would need to loop through the rows to process (such as with a cursor): see below for a sample.
Note that:
1) you should check out Google's terms of use
2) beware that there is a cap of 2,500 lookups per day from a single IP address (unless you make arangements with Google)
3) you would want to modify spGeocode so that it did not return a resultset with each execution (i.e. remove the SELECT @GPSLatitude AS GPSLatitude.... statement)
4) If you are thinking of updating your address data with the formatted address returned by the service, beware that
SET NOCOUNT ON
CREATE TABLE #temp (
RecordID int identity,
Address varchar(50),
City varchar(30),
State varchar(5),
GPSLat numeric(9,6),
GPSLong numeric(9,6),
MapURL varchar(255))
INSERT INTO #temp (Address, City, State)
VALUES ('1150 Magic Way', 'Anaheim', 'CA')
INSERT INTO #temp (Address, City, State)
VALUES ('8039 Beach Boulevard', 'Buena Park', 'CA')
INSERT INTO #temp (Address, City, State)
VALUES ('500 SeaWorld Drive', 'San Diego', 'CA')
INSERT INTO #temp (Address, City, State)
VALUES ('1 Legoland Drive', 'Carlsbad', 'CA')
DECLARE curGeo CURSOR LOCAL STATIC FOR
SELECT RecordID, Address, City, State
FROM #temp
DECLARE @RecordID int
DECLARE @Address varchar(50)
DECLARE @City varchar(30)
DECLARE @State varchar(5)
DECLARE @GPSLatitude numeric(9, 6)
DECLARE @GPSLongitude numeric(9, 6)
DECLARE @MapURL varchar(255)
OPEN curGeo
FETCH curGeo INTO
@RecordID,
@Address,
@City,
@State
WHILE @@FETCH_STATUS = 0 BEGIN
BEGIN TRY
EXEC opsstream.sputilGeocode
@Address = @Address OUTPUT,
@City = @City OUTPUT,
@State = @State OUTPUT,
@GPSLatitude = @GPSLatitude OUTPUT,
@GPSLongitude = @GPSLongitude OUTPUT,
@MapURL = @MapURL OUTPUT
UPDATE #temp
SET
GPSLat = @GPSLatitude,
GPSLong = @GPSLongitude,
MapURL = @MapURL
WHERE
RecordID = @RecordID
END TRY
BEGIN CATCH
PRINT 'Warning: RecordID ' + CAST(@RecordID AS varchar(100)) + ' could not be geocoded.'
END CATCH
FETCH curGeo INTO
@RecordID,
@Address,
@City,
@State
END
SELECT * FROM #temp