Multiple Users & Stored Procedures

[Zukkster]

I want to run a stored procedure that has a number of steps

1) Runs and Insert query that inserts the data into an empty table

2) Execute a Data Transformation package that exports the data from the table to a text file

3) Repeats step 1 and 2 for two further different queries on seperate tables

My question is. If only this stored procedure can update the tables, what happens if two users at the same time try to run different queries using this stored procedure?

I'm assuming SQL server queues the stored procedures executions so that it doesn't run the second time until after it's completed the first execution. Therefore so long as I make sure the two users create text files with different names there will be no need to start playing around with table locks.

Also I'm guessing that this means all the steps need to be in one stored procedure rather than divided into a number of stored procedures

Thanks

 

[SemperFiDownUnda]

They will actually run concurrently when possible. If they they are effecting the same objects then they will serialise where needed.

You can break this SP into multiple storeprocs no problem Just watch how deep you go.

 

[Zukkster]

Ouch. That wasn't what I wanted to hear. Do you know how to lock the tables so that the second user only gets access when the first stored procedure has finished. Otherwise the processes will intefere with each other.

 

[SemperFiDownUnda]

you can use hints to override the normal locking mechanisms

look up the FROM clause in B.O.L. and look at the WITH table_hint statemtent. You can specify a locking method. This can be very expensivebut if you need to assure that certian data does not change while your process goes on then do it.