sprocs execution order - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

sprocs execution order

Author

Topic

BjornOtto
Starting Member

8 Posts

Posted - 2010-09-28 : 10:41:17

I have a sproc (A) that executes 5 other sprocs. The 5 sprocs are not executed in the order they are listed in sproc A. I put them each in a transaction but it does not make a difference. How do I control the execution order?

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2010-09-28 : 10:43:37

How did you find out that procs ran out of order?

Harsh Athalye
http://www.letsgeek.net/

BjornOtto
Starting Member

8 Posts

Posted - 2010-09-28 : 10:47:49

Each proc has a (select current_timestamp) that is written to the table that all 5 procs writes to. The resulting timestamp in the table reveals it.

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2010-09-28 : 10:54:12

Is the parent proc being called from multi-threaded application?

Harsh Athalye
http://www.letsgeek.net/

BjornOtto
Starting Member

8 Posts

Posted - 2010-09-28 : 10:59:54

No. I currently run it from the Management Studio in SQL2005.

BjornOtto
Starting Member

8 Posts

Posted - 2010-09-28 : 11:02:09

Maybe they are executed in the right order but written to the table in different order. If I feed the current_timestamp as a parameter to the procs instead of inside each proc it might work out.

tosscrosby
Aged Yak Warrior

676 Posts

Posted - 2010-09-28 : 15:59:02

are you writing to the table in each executed proc or in the "calling" proc after each execution? I've never seen any issue with select current_timestamp and it should execute them in the same order as within the "calling" proc (never saw it not do that).

Terry

-- You can't be late until you show up.

BjornOtto
Starting Member

8 Posts

Posted - 2010-09-29 : 12:14:41

ProcA and ProcB writes to the logtable with an "insert into... select..." statement. The code in each proc inserts current_timestamp into the logtable. With multiple records in #Updates, sometimes the timestamp written by ProcA is later than the one written by ProcB.

The procedure is basically such simplified:

Create Procedure spUpdateTableAndLogIt
AS
Insert Into #Updates(ID)... Select ID From... Where
If Select count(*) from #Updates > 0 --updates pending
Begin --do the updates
Declare MyCursor Cursor for Select ID From #Updates
Open MyCursor
Fetch Next From MyCursor Into @GetID
While @@FETCH_STATUS = 0
Begin
Exec ProcA @GetID --writes the before-record with timestamp to the log
Update MyTable Set... Were... = @GetID
Exec ProcB @GetID --writes the after-record with timestamp to the log
Fetch Next From MyCursor Into @GetID
End --of cursor
End --of do the updates

BjornOtto
Starting Member

8 Posts

Posted - 2010-09-29 : 12:33:36

I am now forcing the time inside the cursor as a parameter to the procs because I cannot seem fix it any other way:

Declare @UseTime1 as datetime
Declare @UseTime2 as datetime
Set @UseTime1 = (Select current_timestamp)
Set @UseTime2 = DateAdd(s,1,@UseTime1)
....
Exec ProcA @GetID @UseTime1
....
Exec ProcB @GetID @UseTime2
....

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources