Hi All,

I am using one stored procedure which have more than 25 queries in it which returns almost same no. of recordsets. now the problem is that when the execution of stored procedure is going on it takes 5 to 9 seconds so in between this time somebody change something in database using interface (means some values) then it takes the newly changed values. i mean to say that if stored procedure executes 10 queries then the result is of before change any value but in between the time somebody change any value then it takes the changed values for next few or remaining queries. i have used BEGIN TRANS and COMMIT TRANS but it seems not working.

can any body help me on this.

Regards,
Deepak

You need to use the SET TRANSACTION ISOLATION LEVEL command to prevent other users changing the data during your transaction. You probably want to use REPEATABLE READ, but look it up in BOL and choose which level best suits your scenario.

5 - 9 seconds is quite a long time to hold on to locks, if you have a lot of users then blocking updates for this long will not be good for performance...

thanks phil,
do you have any sample code related with same, actually i am new to SQL Server Transactions. And about execution time, how can i reduce it? actually its only the stored procedure in my system which is so longer and even i cant reduce the queires in it.