Utilizing stored procedures

[StevenK]

I'm used to using SQL syntax to generate a result set and then interrogate the generated result set.

I'm now looking at making use of stored procedures in the target SQL Server database but need to be sure that the running of these is waited upon. By this I mean that if I make use of a stored procedure in the 2nd step (block B) of a 3-step code block (run A, then run B, then run C) that code block C is not run before the stored procedure has performed it's workload (be it that it a value returned or a result set returned).
Can I be sure that the stored procedure is completed within step B before step C is started to be run by the application code ?

Is this the case ?

I hope that this makes sense ?

Steve

 

[mikeEd]

Yes; provided the connection is synchronous (which it probably will be)

 

[StevenK]

Would this still hold true if the SQL Server was running (and confirgured to run) on a multi-processor server ?

 

[mikeEd]

yes

 

[dogmaa]

You could also do this asynchronously if you are
using >= ADO 2.5 and >= SQL Server 7.
The OnFetchComplete event of the ADO stored procedure
sets a boolean flag before allowing you
into the finally processing of results.
I have created a test program to demostrate this
as follows:-


Create a ADO connection on a form, add 2 stored
procedures called procUpdate & procResult.
Create a table in database called singlevalue
defined below. Create 2 stored procedures sp_test1
and sp_test2.

Add 2 boolean variables in the form class as follows
FFetchStatus: TEventStatus;
FProcUpdated: boolean;

Add a label, button, OnClick event, OnFetchComplete event as per delphi code below.

--------------------------------------------------------

CREATE TABLE [dbo].[singlevalue] (
[Id] [char] (10) NOT NULL ,
[Value] [int] NULL
) ON [PRIMARY]

--------------------------------------------------------

CREATE PROCEDURE sp_test1 AS
begin
waitfor delay '00:00:10'
update singlevalue set value = value + 1
where id =1
end


CREATE PROCEDURE sp_test2 AS
select value from singlevalue
where id = 1

-------------------------------------------------------

procedure TForm1.btnRunClick(Sender: TObject);

procedure RunCodeBlockA; // Run processing async
begin
FProcUpdated := False;
procUpdate.ExecuteOptions := [eoAsyncExecute];
procUpdate.ExecProc;
end;

procedure RunCodeBlockB; // Could do something useful
var i: integer;
begin
for i := 1 to 10000 do ;
end;

procedure RunCodeBlockC; // Get result Asynchronously
begin
procResult.ExecuteOptions := [eoAsyncFetchNonBlocking];
procResult.Active := True;
while (not FProcUpdated) and
(FFetchStatus <> esErrorsOccured) do
Application.Processmessages;
if (FFetchStatus = esOk) then
lblResultvalue.caption :=
inttostr(procResultvalue.Value)
else
lblResultvalue.caption := 'problem with result';
procResult.Active := False;
end;

begin
btnRun.enabled := False;
RunCodeBlockA;
RunCodeBlockB;
RunCodeBlockC;
btnRun.enabled := True;
end;

procedure TForm1.procResultFetchComplete(DataSet: TCustomADODataSet;
const Error: Error; var EventStatus: TEventStatus);
begin
FFetchStatus := EventStatus;
FProcUpdated := True; // update+fetch completed
end;

-------------------------------------------------------

regards David Champion