May 10, 2012 at 12:36 am
Hi,
This is a strange requirement that the .Net people working in my team came up to me with.
Is there a way that a Stored Procedure can be executed for some time of may be the first few steps, then stopped and then resume execution from where it stopped???
Something like threads. Like stopping a thread, executing another thread and getting back to the first thread.
Can something like that be done with Stored Procedures in SQL Server?
May 10, 2012 at 1:14 am
the only way to pause would be to add a WAITFOR DELAY into the stored proc. It would leave that SPID open so if its doing things inside a transaction would cause you issues with locking/blocking
Something like this to check an order has been added correct for example
CREATE PROC CheckOrderInsertion (@OrdID INT)
AS
SELECT * FROM ORDERS WHERE OrderID = @OrdID --Check that the order header inserted
WAITFOR DELAY '00:01' --Wait for a minute
SELECT * FROM ORDERITEMS WHERE OrderID = @OrdID --Check that the order items inserted
There may be other options at play here, but I'm only aware of WAITFOR
May 10, 2012 at 2:57 am
It's not possible to make stored proc to behave as a multiple threads in client/windows application.
It's possible to stop stored proc execution and continue after some delay as advised in the previous answer. You can even do it in loop and wait, let say, for some external "flag" to set...
However, I wouldn't consider doing that, as it's not really good practice/design. You want your procs to execute as fast as possible and return (exit), so the connection from the caller is not held but returned to the pool for reuse.
I guess your developers lack knowledge in how SQL operates.
If your stored proc does some steps, then calls another stored proc, it will be kind of the 1st stored proc "thread" paused, except that there are no such thing as a thread here. When the called (2nd) proc is finished, the caller stored proc (1st one) will continue to execute whatever steps left.
May 10, 2012 at 3:48 am
May 11, 2012 at 5:15 am
vinu512 (5/10/2012)
Thank you very much.Seems like "WaitForDelay" was what I was looking for.
Uhhm....guys...this may be asking for too much..... :crazy:
But, can send "some kind of a response" to the client application before the "wait for delay" and then delay the procedure and then continue the execution of the procedure after the delay???
The response should be any kind of response.
Actually, the application is running on a 3rd party server. So this 3rd party has a rule that if in any session there would be processing time more than some pre-decided no. of minutes(which only they know how they have calculated) then the Session would get timed out.
This is why our .Net guys have come to a conclusion that what we are trying could work if something could be done with the procedure that it returns some Response to the client application in between execution of blocks of code.
Can this be achieved??....
May 11, 2012 at 5:34 am
Explain why you want delays inside your procedure(s) instead of having them finish as quickly as possible. What you are asking for really makes no sense from a database perspective.
May 11, 2012 at 5:48 am
Lynn Pettis (5/11/2012)
Explain why you want delays inside your procedure(s) instead of having them finish as quickly as possible. What you are asking for really makes no sense from a database perspective.
On second thought I could do without a Delay. Bu, is there a way in which the procedure can send response to the client application after it executes certain blocks of code?
ie: send a request to the client application after every "n" no. of lines of code and then resume.
May 11, 2012 at 5:54 am
Even if it is possible, I am going to go out on a limb here and say it may not be practical to do so.
It all comes back to why. Instead of focusing on sending messages back to the application, focus on writing a stored procedure that accomplishes the necessary task as quickly as possible.
May 11, 2012 at 6:08 am
Lynn Pettis (5/11/2012)
Even if it is possible, I am going to go out on a limb here and say it may not be practical to do so.It all comes back to why. Instead of focusing on sending messages back to the application, focus on writing a stored procedure that accomplishes the necessary task as quickly as possible.
I've got my hands tied here Lynn. Even I agree with what you say.
We had a meeting this morning and I even put forward the solution of changing the procedure. But, at the end of the meeting it was decided to somehow accomplish what the .Net guys are trying to do without altering the procedure.
Even if it is possible, I am going to go out on a limb here and say it may not be practical to do so.
If its not practical to do so, then also I'd like to know how it can be done so that prove to the .Net guys that its impractical. So, that we can collectively look for a better and practical solution rather than bang our heads at this
May 11, 2012 at 6:13 am
Unfortunately, can't give you much more as no one here can see what you see. Unless you show us what you are working with and how you have been directed to change it, all we can do is soot in the dark.
From your own posts:
For better and faster solutions please check..."How to post data/code on a forum to get the best help" - Jeff Moden
May 11, 2012 at 6:24 am
Lynn Pettis (5/11/2012)
Unfortunately, can't give you much more as no one here can see what you see. Unless you show us what you are working with and how you have been directed to change it, all we can do is soot in the dark.From your own posts:
For better and faster solutions please check..."How to post data/code on a forum to get the best help" - Jeff Moden
Lynn, I cannot be more direct with the question. I'll put it as direct as I can get.
Can a Procedure(running in SQL Server) send any kind of response to the Client Application in between execution??? If yes, Then how?
I do not think that is too hard to understand considering your experience with SQL Server.
May 11, 2012 at 6:25 am
Only suggestion I can give you:
http://msdn.microsoft.com/en-us/library/ms131686(v=sql.100).aspx
May 11, 2012 at 6:26 am
By the way, never used MARS, so can't answer any questions about it.
May 11, 2012 at 6:28 am
Personally, I'd concentrate on making the stored procedure more efficient so that it got the job done faster rather than trying to figure out how to send back status messages that are just going to slow down the procedure.
May 11, 2012 at 6:36 am
Lynn Pettis (5/11/2012)
Only suggestion I can give you:http://msdn.microsoft.com/en-us/library/ms131686(v=sql.100).aspx
Thanks for the link Lynn.
I'll see what can be done and get back to you.
Viewing 15 posts - 1 through 15 (of 27 total)
You must be logged in to reply to this topic. Login to reply