By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,842 Members | 2,368 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,842 IT Pros & Developers. It's quick & easy.

How do I use pass data to/from stored procedure

P: n/a
Hello,
I read an article on how to use Yahoos API to GeoCode addresses. Based
on the article I created a stored procedure that is used as follows:
SPGeocode '2121 15st north' ,'arlington' ,'va' ,'warehouse-test'
Returns:
Latitude Longitude GeoCodedCity GeoCodedState GeoCodedCountry
Precision Warning
----------- ---------- ------------- ------------- ---------------
--------------- --------
38.889538 -77.08461 ARLINGTON VA US
Precision Good No Error

It returns Latitude and Longitude and other information. Works great.
In conjunction with Haversine formula, I can compute the distance
between two locations if I know the Lat and Long of the two points.
This can start to answer questions like "How many students do we have
within a 10 mile radius of Location X?"
(Marketing should go nuts over this :)

My question is how can i use my data from a table and pass it to the
SPGeocode via a select statement?
The table I would use is:

CREATE TABLE "dbo"."D_BI_Student"
(
"STUDENT_ADDRESS1" VARCHAR(50) NULL,
"STUDENT_ADDRESS2" VARCHAR(50) NULL,
"STUDENT_CITY" VARCHAR(50) NULL,
"STUDENT_STATE" VARCHAR(10) NULL,
"STUDENT_ZIP" VARCHAR(10) NULL
)
;
This is so new to me, I am not even sure what to search.
TIA
Rob

May 2 '06 #1
Share this Question
Share on Google+
4 Replies


P: n/a
rcamarda (ro*****@hotmail.com) writes:
My question is how can i use my data from a table and pass it to the
SPGeocode via a select statement?
The table I would use is:

CREATE TABLE "dbo"."D_BI_Student"
(
"STUDENT_ADDRESS1" VARCHAR(50) NULL,
"STUDENT_ADDRESS2" VARCHAR(50) NULL,
"STUDENT_CITY" VARCHAR(50) NULL,
"STUDENT_STATE" VARCHAR(10) NULL,
"STUDENT_ZIP" VARCHAR(10) NULL
)
;
This is so new to me, I am not even sure what to search.


If you want to call that procedure for every student in the table, you
would have to set up a cursor over the table and call the proceduire
for each row.

Of course, if it's possible to rewrite that procedure to work on
the entire table directly that's better, but since you seems to be
calling an external API (from an SP???) this may be difficult.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
May 2 '06 #2

P: n/a
Thanks Erland.

For now, I'll try the cursor and one at a time, till I get better
aquanted with the process. I would guess that Yahoo would frown on
batch API calls (they have a 24 hour limit of 50,000 records).
This is what I have so far:

declare @c1 float, @c2 float, @c3 varchar(50), @c4 varchar(50), @state
varchar(10), @zip varchar(10), @country varchar(20)
declare @precision varchar(50), @warning varchar(50)
declare @address1 varchar(50), @city varchar(50)
declare @appid varchar(20)

set @appid = 'warehouse-test'

declare cur cursor fast_forward
for select top 10 student_address1, student_city, student_state,
'warehouse-test'
from student

open cur
fetch next from cur into @address1, @city, @state, @appid
while @@fetch_status = 0
begin
exec SPGeocode @address1, @city, @state, 'warehouse-test'
fetch next from cur into @c1, @c2, @c3, @c4, @state, @zip,
@country, @precision, @warning
end
close cur
deallocate cur

When I execute, I get one row returned, then an error:

Msg 16924, Level 16, State 1, Line 17
Cursorfetch: The number of variables declared in the INTO list must
match that of selected columns.
My confusion is that I need 4 parameters for the procedure, but it
returns more columns.
Do I need to pad the columns, simular to have same number columns when
using a UNION with selects?
TIA
Rob

May 3 '06 #3

P: n/a
Update:
this is returning rows, but its like 10 separeate selects. I need to
get this into a table

declare @c1 float, @c2 float, @c3 varchar(50), @c4 varchar(50), @state
varchar(10), @zip varchar(10), @country varchar(20)
declare @precision varchar(50), @warning varchar(50)
declare @address1 varchar(50), @city varchar(50)
declare @appid varchar(20)
declare @char1 varchar(20), @char2 varchar(20), @char3 varchar(20),
@char4 varchar(20)

set @appid = 'warehouse-test'

declare cur cursor fast_forward
for select top 10 student_address1, student_city, student_state,
'warehouse-test'
from student

open cur
fetch next from cur into @address1, @city, @state, @appid
while @@fetch_status = 0
begin
exec SPGeocode @address1, @city, @state, 'warehouse-test'
fetch next from cur into @address1, @city, @state, @appid
end
close cur
deallocate cur

May 3 '06 #4

P: n/a
rcamarda (ro*****@hotmail.com) writes:
this is returning rows, but its like 10 separeate selects. I need to
get this into a table


Have a look at http://www.sommarskog.se/share_data.html for suggestions.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
May 3 '06 #5

This discussion thread is closed

Replies have been disabled for this discussion.