• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 378
  • 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
Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

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