Running Mulitple stored procedures

Transact-SQL
1

I have three stored procedures.

proc1 creates a table, then select into with data, then creates the indexes for a table.
proc2 creates a table, then select into with data, then creates the indexes for a table.
proc3 deletes and updates the records created in proc1 and 2

I have another proc that EXEC each of the above.

What I am getting is if I run Proc1, 2 and 3, the actions in 3 are not doing anything.
If I run 1 and 3 it works fine.
It is like I cannot run three procs after each other.

If after I run Proc1, 2 and 3. I wait; running proc3 manually, it works fine.

Just for your information I am running this on SQL server 2019, and the table in Proc1 contains about 25K records and proc2 contains 24K records.

Any ideas or suggestions to what is happening?

2

There shouldn't be a problem with that. Maybe proc3 is rolling back somehow??

Would have to have more details to be able to give you anything more specific.

1 Like
3

How are the 3 procs being executed? Are you connecting with an application in Java or Python or something, then opening a connection, executing proc1 and/or proc2, then with that connection still open, opening another connection to execute proc3?

4

It should work and may be more details are required to check on the reason why it's acting like that .

From what i I understand the following is schema which you hade mentioned

Create Proc Main
As
Begin

Exec testSP1
Exec  testSP2
Exec testSP3

End

And what exists in the those sub stored procs are like

Create Proc testSP1
Begin
If exist drop temp1
Select * into temp1 from dbo.[source1]
Create index idx1..
End

Create Proc testSP2
Begin
If exist drop temp2
Select * into temp2 from dbo.[source2]
Create index idx1..
End

Create Proc testSP3
Begin
Delete from temp1
Update temp2 Set col1 = 'test'
End

If this is how it's structured then it should work .. let me know if you have more details .
Thanks