• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 372
  • Last Modified:

Call a stored procedure inside a stroed procedure and verify data

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
,@addr1=[ShippingAddressLine1]
      ,@addr2=[ShippingAddressLine2]
      ,@city=[City]
      ,@state=[State]
      ,@zip=[Zip] from addresstable
      where  inprocess=0

#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.
0
troycomp
Asked:
troycomp
  • 4
  • 4
  • 2
2 Solutions
 
Ephraim WangoyaCommented:
try this
declare @pos int
declare @orderid int
declare @addr1 varchar(100)
declare @addr2 varchar(50)
declare @city varchar(50)
declare @state varchar(5)
declare @zip varchar(5)

set @pos = 1

while exists(select 1 from addresstable where orderid >= @pos)
begin
	select top 1
		@pos = orderid
		,@orderid = orderid
		,@addr1=[ShippingAddressLine1]
		,@addr2=[ShippingAddressLine2]
		,@city=[City]
		,@state=[State]
		,@zip=[Zip] 
	from addresstable
	where  inprocess=0
	and orderid >= @pos
	
	set @pos = @pos + 1
	
	--Add your code
	
end

Open in new window

0
 
lomo74Commented:
@ewangoya:
that's completely wrong, LoL
what's that loop supposed to do?

@troycomp:
this could be your solution...
you have to complete the VerifyAddress procedure with you real check on the address
if exists (select name from sysobjects where type = 'P' and name = 'VerifyAddress')
	drop procedure VerifyAddress
go

create procedure VerifyAddress
	@addr1 varchar(100),
	@addr2 varchar(50),
	@city varchar(50),
	@state varchar(5),
	@zip varchar(5)
as begin
set nocount on

declare @ret int

-- perform your check here

-- set an appropriate return value
-- e.g.  0 = OK    1 = bad address
-- now we just simulate bad addresses for testing the stored proc
set @ret = 1

return @ret
end
go

if exists (select name from sysobjects where type = 'P' and name = 'MainProcedure')
	drop procedure MainProcedure
go

create procedure MainProcedure
as begin
set nocount on

declare @orderid int
declare @addr1 varchar(100)
declare @addr2 varchar(50)
declare @city varchar(50)
declare @state varchar(5)
declare @zip varchar(5)
declare @result int

begin transaction

declare curs cursor local forward_only for
	select	orderid,
		ShippingAddressLine1,
		ShippingAddressLine2,
		City,
		State,
		Zip
	from	addresstable
	where 	inprocess = 0
	
open curs

-- read first record
fetch next from curs
into @orderid, @addr1, @addr2, @city, @state, @zip

while @@fetch_status = 0 begin
	-- execute check
	exec @result = VerifyAddress @addr1, @addr2, @city, @state, @zip

	-- flag as "bad address" the record that did not pass the check
	if @result <> 0 begin
		update	addresstable
		set	badaddress = 1
		where	current of curs
		
		-- if an error occurred, handle it
		if @@error <> 0 goto Err
	end
	
	-- move on
	fetch next from curs
	into @orderid, @addr1, @addr2, @city, @state, @zip
end

-- all went fine... commit
commit transaction

close curs
deallocate curs

return 0

-- error handling
Err:
rollback transaction

raiserror('Something went wrong',15,1) with seterror

close curs
deallocate curs

return 1
end
go

Open in new window

0
 
lomo74Commented:
@troycomp:
besides giving a working solution, here's some background --
what you were looking for is the CURSOR.
as you can see from the code above: you declare a CURSOR over a SELECT statement; then you iterate through the selected records using the FETCH statement.
the global variable @@FETCH_STATUS contains the result of the last FETCH: 0=no error   -1=fetch failed   -2=row missing.
as long as the FETCH succeeds, you move on to the next record.
you keep your record's fields into variables (@orderid, @addr1, and so on), that you can use to inspect your data.
the whole operation is under the control of a TRANSACTION: all operations succeed, or they fail alltogether.
as a last comment, look at the syntax of the update statement: the WHERE clause uses the CURRENT OF curs expression because you are updating the same table that you are traversing with the cursor, so you can use the cursor itself to identify the record to be updated.

some references:
http://msdn.microsoft.com/en-us/library/ms180169.aspx
http://msdn.microsoft.com/en-us/library/ms177523.aspx
http://msdn.microsoft.com/en-us/library/ms187308.aspx
http://msdn.microsoft.com/en-us/library/ms188929.aspx

oh, and the while loop --
it's even better to declare it as follows:
while @@fetch_status <> -1 begin
  if @@fetch_status <> -2 begin
    -- do your computations here
  end
  fetch next from curs
  into ... blah blah ...
end

Open in new window

this way: an error (-1) breaks the whole loop; a missing record (-2) simply moves on to the next record.
0
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 
troycompAuthor Commented:
Hi Guys,

We are having server issues at my job, so I cant run my sprocs right now. When the servers are back up and running, I'll get back on this cause this is a HOT topic at my job right now. Please bare with me
0
 
Ephraim WangoyaCommented:
@lomo74

The problem is that you do not understand the code whatsoever.
You use a cursor, I choose not to.

Look at the loop again
0
 
lomo74Commented:
oh, yes. you are correct, it works. sorry.
but it is quite obscure, isn't it?
I think at least the use of cursors leads to more readable code.
do you have any reason for not using them?
0
 
Ephraim WangoyaCommented:

cursors generally tend to use a lot of sql server resources, so I avoid them as much as possible
0
 
Ephraim WangoyaCommented:

Here is an interesting article to browse on cursors vs select

http://www.sqlteam.com/article/cursor-performance
0
 
lomo74Commented:
interesting, I'm giving it a reading. thank you for the tip.
anyway; I would prefer readability rather than performance in this context.
0
 
troycompAuthor Commented:
Sorry for the delay, man its been crazy here at work.

@ewangoya

Your way worked perfectly. Thanks you very much.

@lomo74:
I didnt try your approach, but i see you agree with ewangoya:. Thanks

0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 4
  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now