How to check whether a stored procedure has completed all the inserting/deleting/updating operations?

If the stored procedure returns, all operations for that call are complete.

Up vote 2 down vote favorite share g+ share fb share tw.

I have got a stored procedure in SQL Server 2008 and it does quite a fair amount of inserting / deleting / updating operations. Now I am wondering if there would be any way that I might be able to detect whether or not a stored procedure has completed ALL Inserting / Deleting / Updating operations. Also, I understand that there can be a returned value from a stored procedure, which in this case here can be a statusCode (0/1).

But through some of my experiments, I found that the statusCode always would get returned immediately once the execution of the stored procedure was finished, while in the mean time, inserting / deleting / updating was actually still running. So what should I do here to see the statusCode only get returned when inserting / deleting / updating operations have all been completed? Thanks.

Code Structure: BEGIN DECLARE @statusCode SET @statusCode = 0 -- Loop through all tables in a given database -- using cursor -- do Insert / Update/ Delete operations SET @statusCode = 1 SELECT @statusCode END sql-server tsql stored-procedures return-value link|improve this question edited Jun 27 '11 at 9:27 asked Jun 27 '11 at 8:53woodykiddy5721112 83% accept rate.

It is considered polite to accept answers to your questions. To a large extent, you have neglected to do so. You may want to go back and accept answers to your previous questions.

This may motivate further help from other StackOverflow users. – Rodrigo Jun 27 '11 at 9:35 @Rodrigo Thanks for the reminder. Yeah I have marked the answer for this post and will review previous posts and mark those that haven't been marked yet.

– woodykiddy Jun 27 '11 at 9:41.

If the stored procedure returns, all operations for that call are complete. You have not seen operations continuing after a stored procedure finishes unless another connection is making changes too. For one, it would break A in ACID.

Gbn Hmm that's interesting. I ran the stored procedure in SQL server 2008 and actually I'd seen it return the value even before query execution was complete. – woodykiddy Jun 27 '11 at 9:06 @woodykiddy: this is impossible.

It is that simple. – gbn Jun 27 '11 at 9:08 @gbn thanks for the wiki link, and I am reading it. – woodykiddy Jun 27 '11 at 9:09 @gbn I had a look at the code in that stored procedure, and I found the variable set @statusCode=0 at the very beginning of it and set @statusCode=1 select @statusCode at the bottom.

Technically, is it really returning a value? – woodykiddy Jun 27 '11 at 9:17 Yes, a single row, single column recordset. Maybe expand your question with code (don't shorten it toomuch) and explain your symptoms.

Do you have multiple callers to this procedure but only need one to run it at once? – gbn Jun 27 '11 at 9:19.

I cant really gove you an answer,but what I can give you is a way to a solution, that is you have to find the anglde that you relate to or peaks your interest. A good paper is one that people get drawn into because it reaches them ln some way.As for me WW11 to me, I think of the holocaust and the effect it had on the survivors, their families and those who stood by and did nothing until it was too late.