Returning intermediate results from a stored procedure

I am executing a stored procedure from a VB application, and would like to r
eturn
"intermediate" results from the stored procedure. Something along the lines
of:
--begin stored procedure
SELECT . . .
--display "done with select" in VB app
UPDATE . . .
--display "done with update" in VB app
DELETE . . .
--display "done with delete" in VB app
-- end stored procedure
you get the idea. I know I can do this by splitting each SQL statement into
a separate
stored procedure and executing each one from VB, but it seems there must be
a better way.
The only thing I've come up with at this point is to update a 1-row 1 column
SQL Server
table as each step completes, as follows:
UPDATE Progress SET Message = "Now done with SELECT/UPDATE/DELETE etc."
and refresh a label on a form every x number of seconds with the contents of
that column.
Any thoughts/suggestions/advice welcome.
Thanks in advance --
Carl Imthurn"Carl Imthurn" <nospam@.all.com> wrote in message
news:uo5FVUcGFHA.1176@.TK2MSFTNGP12.phx.gbl...
> I am executing a stored procedure from a VB application, and would like to
return
> "intermediate" results from the stored procedure. Something along the
lines of:
> --begin stored procedure
> SELECT . . .
> --display "done with select" in VB app
> UPDATE . . .
> --display "done with update" in VB app
> DELETE . . .
> --display "done with delete" in VB app
> -- end stored procedure
> you get the idea. I know I can do this by splitting each SQL statement
into a separate
> stored procedure and executing each one from VB, but it seems there must
be a better way.
> The only thing I've come up with at this point is to update a 1-row 1
column SQL Server
> table as each step completes, as follows:
> UPDATE Progress SET Message = "Now done with SELECT/UPDATE/DELETE etc."
> and refresh a label on a form every x number of seconds with the contents
of that column.
> Any thoughts/suggestions/advice welcome.
> Thanks in advance --
> Carl Imthurn
>
Completely impossible, I would suggest, other than using a solution such as
you have already come up with.
What's wrong with the three-steps-in-the-client approach? Do they even need
to be SP's? It seems a lot more elegant your update-and-poll-a-table idea.|||Thanks for your reply.
I'm leaning away from the steps-in-the-client approach only because it would
be in
actuality 10 steps (or more) rather than the three I mentioned. Apologies fo
r the
miscommunication.
They don't necessarily need to be SPs, but if the client simply executed a S
QL statement,
the logged-in user would have to have permissions to the tables themselves,
rather than
simply EXECUTE permission to the SPs.
Brian wrote:
>
> Completely impossible, I would suggest, other than using a solution such a
s
> you have already come up with.
> What's wrong with the three-steps-in-the-client approach? Do they even ne
ed
> to be SP's? It seems a lot more elegant your update-and-poll-a-table idea
.
>|||Theoretically, you can add RAISERROR command in between and use the NOWAIT o
ption to the RAISERROR
command. This means that SQL Server flushes the output buffer so the data in
the buffer so far is
sent to the client. In order to take advantage of this in the client applica
tions, you need to
program asynchronously (obviously, otherwise the call to the db API function
is just sitting there
until the whole procedure has executed). Try below from QA and you'll see th
e difference:
Compare below:
PRINT 'hello'
WAITFOR DELAY '00:00:7'
PRINT 'There'
With below:
RAISERROR ('hello', 10, 1) WITH NOWAIT
WAITFOR DELAY '00:00:7'
PRINT 'There'
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Carl Imthurn" <nospam@.all.com> wrote in message news:OYZB%23IdGFHA.4088@.TK2MSFTNGP09.phx.g
bl...
> Thanks for your reply.
> I'm leaning away from the steps-in-the-client approach only because it wou
ld be in actuality 10
> steps (or more) rather than the three I mentioned. Apologies for the misco
mmunication.
> They don't necessarily need to be SPs, but if the client simply executed a
SQL statement, the
> logged-in user would have to have permissions to the tables themselves, ra
ther than simply EXECUTE
> permission to the SPs.
> Brian wrote:
>|||Thanks for your reply, Tibor.
I tried both of them in QA and for some reason they both waited 7 seconds an
d then printed
'hello' and 'there' at the same time.
I don't know if a configuration setting needs to be changed, but regardless
of that,
reading the rest of your post, you lost me, technically speaking.
(my fault, not yours, I assure you) :-)
Anyway, thanks again for your time - I appreciate it.
Carl
Tibor Karaszi wrote:

> Theoretically, you can add RAISERROR command in between and use the NOWAIT
option to the RAISERROR
> command. This means that SQL Server flushes the output buffer so the data
in the buffer so far is
> sent to the client. In order to take advantage of this in the client appli
cations, you need to
> program asynchronously (obviously, otherwise the call to the db API functi
on is just sitting there
> until the whole procedure has executed). Try below from QA and you'll see
the difference:
> Compare below:
> PRINT 'hello'
> WAITFOR DELAY '00:00:7'
> PRINT 'There'
>
> With below:
> RAISERROR ('hello', 10, 1) WITH NOWAIT
> WAITFOR DELAY '00:00:7'
> PRINT 'There'
>|||"Carl Imthurn" <nospam@.all.com> wrote in message
news:umE%23yifGFHA.3612@.TK2MSFTNGP09.phx.gbl...
> Thanks for your reply, Tibor.
> I tried both of them in QA and for some reason they both waited 7 seconds
and then printed
> 'hello' and 'there' at the same time.
> I don't know if a configuration setting needs to be changed, but
regardless of that,
> reading the rest of your post, you lost me, technically speaking.
> (my fault, not yours, I assure you) :-)
> Anyway, thanks again for your time - I appreciate it.
> Carl
> Tibor Karaszi wrote:
>
NOWAIT option to the RAISERROR
data in the buffer so far is
applications, you need to
function is just sitting there
see the difference:
>
Hi Carl,
I understand your earlier reply: all your points make sense.
Tibor's suggestion is neat, I like it, I've filed it away for future use
myself. However, you do need to take careful note of his point "you need to
program asynchronously". You haven't said what technology you are using
(ADO.Net, ADO, DAO or whatever) but it shouldn't be hard to figure out how
to run a command or query asynchronously.|||Thanks to Brian for catching up on the programming aspects.

> I tried both of them in QA and for some reason they both waited 7 seconds
and then printed 'hello'
> and 'there' at the same time.
Did you execute into grid or text? On my machine (SQL2K with sp3) I did get
a pause between the two
(text mode).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Carl Imthurn" <nospam@.all.com> wrote in message news:umE%23yifGFHA.3612@.TK2MSFTNGP09.phx.g
bl...
> Thanks for your reply, Tibor.
> I tried both of them in QA and for some reason they both waited 7 seconds
and then printed 'hello'
> and 'there' at the same time.
> I don't know if a configuration setting needs to be changed, but regardles
s of that, reading the
> rest of your post, you lost me, technically speaking.
> (my fault, not yours, I assure you) :-)
> Anyway, thanks again for your time - I appreciate it.
> Carl
> Tibor Karaszi wrote:
>
>|||Hi guys --
A couple of things:
1) In regards to Tibor's suggestion:
Theoretically, you can add RAISERROR command in between and use the
NOWAIT option to the RAISERROR command. This means that SQL Server
flushes the output buffer so the data in the buffer so far is sent to
the client. In order to take advantage of this in the client
applications, you need to program asynchronously (obviously, otherwise
the call to the db API function is just sitting there until the whole
procedure has executed). Try below from QA and you'll see the difference:
I executed those commands again in QA and this time I got the expected resul
ts:
PRINT 'hello'
WAITFOR DELAY '00:00:7'
PRINT 'There'
the above code printed 'hello' and 'There' at the same time; after 7 seconds
.
the following code printed 'hello' immediately and 'There' after 7 seconds.
RAISERROR ('hello', 10, 1) WITH NOWAIT
WAITFOR DELAY '00:00:7'
PRINT 'There'
So, no idea what happened the first time on my end, but now I understand wha
t you mean.
Sorry about the mixup on my end.
2) In regards to Brian's message:
I'm using ADO. Here's the relevant code:
Dim Cmd As ADODB.Command
Set Cmd = New ADODB.Command
Cmd.CommandText = "si_Import_APTMST_FromAS400"
Cmd.CommandType = adCmdStoredProc
Cmd.ActiveConnection = pADOConnection ' already created earlier in code
Cmd.Execute
Set Cmd = Nothing
How do I execute this asynchronously? If you could steer me in the right dir
ection (a web
page describing asynchronous execution, a newsgroup posting, etc.) I would b
e in your debt.
Thanks to both of you again for your time and expertise.
PS I looked at the timestamp of your postings and thought goodness gracious,
don't these
guys ever sleep? But from Brian's e-mail address he appears to be in the UK,
and if I
remember correctly Tibor, you're in Sweden right now. Anyway, guys, thanks a
lot.
Carl|||"Carl Imthurn" <nospam@.all.com> wrote in message
news:eT7oKJpGFHA.2924@.TK2MSFTNGP15.phx.gbl...
> Hi guys --
> A couple of things:
> 1) In regards to Tibor's suggestion:
> Theoretically, you can add RAISERROR command in between and use the
> NOWAIT option to the RAISERROR command. This means that SQL Server
> flushes the output buffer so the data in the buffer so far is sent to
> the client. In order to take advantage of this in the client
> applications, you need to program asynchronously (obviously, otherwise
> the call to the db API function is just sitting there until the whole
> procedure has executed). Try below from QA and you'll see the difference:
> I executed those commands again in QA and this time I got the expected
results:
> PRINT 'hello'
> WAITFOR DELAY '00:00:7'
> PRINT 'There'
> the above code printed 'hello' and 'There' at the same time; after 7
seconds.
> the following code printed 'hello' immediately and 'There' after 7
seconds.
> RAISERROR ('hello', 10, 1) WITH NOWAIT
> WAITFOR DELAY '00:00:7'
> PRINT 'There'
> So, no idea what happened the first time on my end, but now I understand
what you mean.
> Sorry about the mixup on my end.
> 2) In regards to Brian's message:
>
how
> I'm using ADO. Here's the relevant code:
> Dim Cmd As ADODB.Command
> Set Cmd = New ADODB.Command
> Cmd.CommandText = "si_Import_APTMST_FromAS400"
> Cmd.CommandType = adCmdStoredProc
> Cmd.ActiveConnection = pADOConnection ' already created earlier in
code
> Cmd.Execute
> Set Cmd = Nothing
> How do I execute this asynchronously? If you could steer me in the right
direction (a web
> page describing asynchronous execution, a newsgroup posting, etc.) I would
be in your debt.
> Thanks to both of you again for your time and expertise.
> PS I looked at the timestamp of your postings and thought goodness
gracious, don't these
> guys ever sleep? But from Brian's e-mail address he appears to be in the
UK, and if I
> remember correctly Tibor, you're in Sweden right now. Anyway, guys, thanks
a lot.
> Carl
>
Hi Carl,
I'm no ADO expert (by the time I thought about moving on from DAO, dotnet
was on it's way so there didn't seem much point in learning ADO!)
However, I understand that a command is executed asynchronously by
specifying the adAsyncExecute option for the Execute method. The command
object will raise an ExecuteComplete event when it's done.|||Thanks Brian -- I will check that out.
Brian wrote:
> "Carl Imthurn" <nospam@.all.com> wrote in message
> news:eT7oKJpGFHA.2924@.TK2MSFTNGP15.phx.gbl...
>
> results:
>
> seconds.
>
> seconds.
>
> what you mean.
>
> how
>
> code
>
> direction (a web
>
> be in your debt.
>
> gracious, don't these
>
> UK, and if I
>
> a lot.
>
>
> Hi Carl,
> I'm no ADO expert (by the time I thought about moving on from DAO, dotnet
> was on it's way so there didn't seem much point in learning ADO!)
> However, I understand that a command is executed asynchronously by
> specifying the adAsyncExecute option for the Execute method. The command
> object will raise an ExecuteComplete event when it's done.
>