How do I use pass data to/from stored procedure

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

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

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

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