Controlling flow in a stored procedure

I have a stored procedure with two UPDATE statements in it. The second UPDATE statement relies on the completion of the first UPDATE statement to run correctly.

The problem I am running into is that SQL Server sometimes runs the second statement before completing the first.

To get around this, I tried putting the second UPDATE statement in a different stored procedure called within the first procedure, but I am still having problems.

I do not believe I am doing anything wrong, but just in case, here is the relevant code from the proc:

-- Look up County ID

BEGIN TRANSACTION

UPDATE tmpZoneTypes

SET CountyID =

(SELECT CountyID

FROM tblCountyLkp

WHERE tblCountyLkp.CountyName = LTRIM(RTRIM(tmpZoneTypes.CountyName)))

COMMIT TRANSACTION

-- Look up existing Zone Type IDs

BEGIN TRANSACTION

UPDATE tmpZoneTypes

SET ZoneTypeID =

(SELECT tblZoneTypes.ZoneTypeID

FROM tblZoneTypes

WHERE tblZoneTypes.CountyID = tmpZoneTypes.CountyID

AND tblZoneTypes.FieldNbr = tmpZoneTypes.FieldNbr

AND LTRIM(RTRIM(tblZoneTypes.ZoneAbbrev)) = LTRIM(RTRIM(tmpZoneTypes.ZoneAbbrev))

AND LTRIM(RTRIM(tblZoneTypes.ZoneFull)) = LTRIM(RTRIM(tmpZoneTypes.ZoneFull)))

COMMIT TRANSACTION

Is there a way to control the flow so the second update statement won't run until the first statement has been completed? I thought about maybe using a trigger to fire whenever the CountyID field is updated. Other options?

chris

1 variant (for SQL 2000 & SQL 2005):

begin transaction

declare @.ErrorVar int

update .... --The First Update

set @.ErrorVar = @.@.Error

if @.ErrorVar <>0

begin

-- Insert your error hadling code

rollback --For Example rollback transaction

end

else

begin

update ... --The second update

commit

end

2 variant (for SQL 2005 only):

begin tran

begin try

update ... --The first update

--If you have error in fist update you go to catch block

update ... - The second update

commit

end try

begin catch

-- Insert your error hadling code

rollback --For Example rollback transaction

end catch

|||SQL always executes "top down" and completes the first statement before starting the 2nd. What makes you think it is not complete?

The only way I see you would get different results than expected with what you posted, would be if you have the isolation level set to "read uncommitted". You can set the isolation level by using:

SET TRANSACTION ISOLATION LEVEL

SERIALIZABLE

at the top of your stored proc and that will force all updates to be committed and locks to be placed on the data until you are done.|||

Thanks for the suggestions from both of you. It turns out the problem was a bug in a subsequent UPDATE statement that was changing my ZoneTypeID back to NULL. I fixed the bug, and now the proc works perfectly.

chris