returning status periodically from stored procedure

I'm fairly new at anything ambitious in SQL. I can set up tables and create
basic stored procedures, triggers and such. I know my way around ok, but
there is a lot to learn. I'm just curious about something. Nothing to show
you in particular becuase it's not been started but I want to know if 2
things are possible with stored procedures.
1. Can a stored procedure pass a result to the front end more than once
during execution? I want to write a procedure that does some processing that
might take more than just a few seconds. I want the front end to display a
status bar. If the procedure has say 5 steps in it, can I send out a result
at the end of each step so I can increment my front end status bar. If not,
other than doing the processing on the front end, what is the standard way
to accomplish this?
2. Use the same situation above. But now I'd like to have a cancel button on
a form in the front end so that if a backend process takes too long the use
can cancel it before it completes. Is there a way to cancel a stored
procedure from the front end while that procedure is executing?
Thanks,
Keith1.SP are executed as a whole or not, so a status bar is not possible, you
can fire your single queries to your db (each in a seperate SP) and use the
statusbar for that.
2. Like in the above situation, executing a procedure will force some
applications to wait for the execution until the result gets back. Sure
there is a possibilities to use different threads, but I dont think that
you mean that. There will be a chang in VS.NET 2005 to fire up queries
asynchronly but I dont know about anything in other Data Providers about
it.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Keith G Hicks" <krh@.comcast.net> schrieb im Newsbeitrag
news:uAPJ7AlWFHA.2520@.TK2MSFTNGP09.phx.gbl...
> I'm fairly new at anything ambitious in SQL. I can set up tables and
> create
> basic stored procedures, triggers and such. I know my way around ok, but
> there is a lot to learn. I'm just curious about something. Nothing to show
> you in particular becuase it's not been started but I want to know if 2
> things are possible with stored procedures.
> 1. Can a stored procedure pass a result to the front end more than once
> during execution? I want to write a procedure that does some processing
> that
> might take more than just a few seconds. I want the front end to display a
> status bar. If the procedure has say 5 steps in it, can I send out a
> result
> at the end of each step so I can increment my front end status bar. If
> not,
> other than doing the processing on the front end, what is the standard way
> to accomplish this?
> 2. Use the same situation above. But now I'd like to have a cancel button
> on
> a form in the front end so that if a backend process takes too long the
> use
> can cancel it before it completes. Is there a way to cancel a stored
> procedure from the front end while that procedure is executing?
> Thanks,
> Keith
>|||Just as I suspected. Thanks for the quick response. -keith :)
"Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote in
message news:OEWtnKlWFHA.3140@.TK2MSFTNGP14.phx.gbl...
1.SP are executed as a whole or not, so a status bar is not possible, you
can fire your single queries to your db (each in a seperate SP) and use the
statusbar for that.
2. Like in the above situation, executing a procedure will force some
applications to wait for the execution until the result gets back. Sure
there is a possibilities to use different threads, but I dont think that
you mean that. There will be a chang in VS.NET 2005 to fire up queries
asynchronly but I dont know about anything in other Data Providers about
it.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Keith G Hicks" <krh@.comcast.net> schrieb im Newsbeitrag
news:uAPJ7AlWFHA.2520@.TK2MSFTNGP09.phx.gbl...
> I'm fairly new at anything ambitious in SQL. I can set up tables and
> create
> basic stored procedures, triggers and such. I know my way around ok, but
> there is a lot to learn. I'm just curious about something. Nothing to show
> you in particular becuase it's not been started but I want to know if 2
> things are possible with stored procedures.
> 1. Can a stored procedure pass a result to the front end more than once
> during execution? I want to write a procedure that does some processing
> that
> might take more than just a few seconds. I want the front end to display a
> status bar. If the procedure has say 5 steps in it, can I send out a
> result
> at the end of each step so I can increment my front end status bar. If
> not,
> other than doing the processing on the front end, what is the standard way
> to accomplish this?
> 2. Use the same situation above. But now I'd like to have a cancel button
> on
> a form in the front end so that if a backend process takes too long the
> use
> can cancel it before it completes. Is there a way to cancel a stored
> procedure from the front end while that procedure is executing?
> Thanks,
> Keith
>|||I don't think that this is true, not in the strictest of sense. True what
he is suggesting is not a good idea, but certainly result sets or error
messages can be returned, and parts of procs can be completed or ignored
based on what happens in the procedure.
As an example, consider error messages. They have to be answered before the
procedure continues on. Same with count messages. If you return 2 result
sets, you can stop retrieving data and never get to the second call (and
subsequently not ever run a statement that is more important, like a commit
transaction. This bit me once in a pooled connection app. Had to roll back
several hours of work!) In fact, if you don't retrieve all of the rows from
the first result set (if there are > 1) then it will never get to the
second.
Even in 2005, a single query batch will be synchronous, but you can run two
different batches asynch on the same connection (or a reasonable facimile
thereof.)
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote in
message news:OEWtnKlWFHA.3140@.TK2MSFTNGP14.phx.gbl...
> 1.SP are executed as a whole or not, so a status bar is not possible, you
> can fire your single queries to your db (each in a seperate SP) and use
> the statusbar for that.
> 2. Like in the above situation, executing a procedure will force some
> applications to wait for the execution until the result gets back. Sure
> there is a possibilities to use different threads, but I dont think that
> you mean that. There will be a chang in VS.NET 2005 to fire up queries
> asynchronly but I dont know about anything in other Data Providers about
> it.
> --
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
> "Keith G Hicks" <krh@.comcast.net> schrieb im Newsbeitrag
> news:uAPJ7AlWFHA.2520@.TK2MSFTNGP09.phx.gbl...
>|||Basically, you would return multiple resultsets, and the ADO command would
need to be called asynchronously in order to update a status and allow
cancellation. Never done this before, so I'm not sure about the details.
When you get it working, then post back with your final solution.
"Keith G Hicks" <krh@.comcast.net> wrote in message
news:uAPJ7AlWFHA.2520@.TK2MSFTNGP09.phx.gbl...
> I'm fairly new at anything ambitious in SQL. I can set up tables and
create
> basic stored procedures, triggers and such. I know my way around ok, but
> there is a lot to learn. I'm just curious about something. Nothing to show
> you in particular becuase it's not been started but I want to know if 2
> things are possible with stored procedures.
> 1. Can a stored procedure pass a result to the front end more than once
> during execution? I want to write a procedure that does some processing
that
> might take more than just a few seconds. I want the front end to display a
> status bar. If the procedure has say 5 steps in it, can I send out a
result
> at the end of each step so I can increment my front end status bar. If
not,
> other than doing the processing on the front end, what is the standard way
> to accomplish this?
> 2. Use the same situation above. But now I'd like to have a cancel button
on
> a form in the front end so that if a backend process takes too long the
use
> can cancel it before it completes. Is there a way to cancel a stored
> procedure from the front end while that procedure is executing?
> Thanks,
> Keith
>