Call a stored procedure inside a stroed procedure and verify data
Posted on 2011-10-19
I need to write a sproc that:
#1. selects records from a table:
declare @orderid int
declare @addr1 varchar(100)
declare @addr2 varchar(50)
declare @city varchar(50)
declare @state varchar(5)
declare @zip varchar(5)
select @orderid = orderid
,@zip=[Zip] from addresstable
#2. calls a sproc for each record selected from #1 :
foreach record in #1
exec dbo.VerifyAddress @addr1 , @addr2, @city , @state, @zip
#3. Check the results from the sproc in #2 to see if the record is valid
set @result = resultField from VerifyAddress sproc
4 if its not valid, update a field in a table and move on to the next record selected from #1
if @result == 'bad address'
update addresstable set badaddress=1 where addressid='id from the current record'
get next record from #1 and do this process again until all records from select statement are read.
I've googled but I cant find a total solution. I know how to call a sproc from within a sproc, update a record and such, but i dont know how to loop through a select statement one at a time so i can see what records are bad. I hope someone can help me. Thanks for reading.