Board index » delphi » Execute more than one Stored Procedure in one query

Execute more than one Stored Procedure in one query

Execute more than one Stored Procedure in one query. Can it be done?

qryCopyDayToPeriod := TQuery.Create (Self);
qryCopyDayToPeriod.Databasename := qryProductPrice.DatabaseName;
qryCopyDayToPeriod.SQL.Clear;
qryCopyDayToPeriod.SQL.Add ('sp_dboption databasename, ''select
into/bulkcopy'', TRUE');  // Disable inserts in the LOG
qryCopyDayToPeriod.SQL.Add ('CopyDayToPeriod :OldProductNumber, :SourceDate,
:DestStartDate, :DestEndDate');
qryCopyDayToPeriod.SQL.Add ('sp_dboption databasename, ''select
into/bulkcopy'', FALSE'); // Enable inserts in the LOG
qryCopyDayToPeriod.ParamByName('OldProductNumber').AsInteger := ProductNumber;
qryCopyDayToPeriod.ParamByName('SourceDate').AsDate := SourceDate;
qryCopyDayToPeriod.ParamByName('DestStartDate').AsDate := DestStartDate;
qryCopyDayToPeriod.ParamByName('DestEndDate').AsDate := DestEndDate;
qryCopyDayToPeriod.ExecSQL;
qryCopyDayToPeriod.Free;

Here I have 3 Stored Procedure calls in a row. Can it be done? Do I have to
write something inbetween or do I have to execute them one at a time?

Jacob Pedersen

 

Re:Execute more than one Stored Procedure in one query


In Interbase you can call SP from other SP.

--
Yours sincerely, Aleksey Emelyanov
Software developer
Altay Customs,
Barnaul, Russia
e-mail: casper....@usa.net
www: http://www.ab.ru/~casper
ICQ UIN: 44960044

Quote
Jacob Pedersen <J.Peder...@techotel.dk> wrote in message

news:8EDBA088Cjpedersentechotel@207.105.83.62...
Quote
> Execute more than one Stored Procedure in one query. Can it be done?

> qryCopyDayToPeriod := TQuery.Create (Self);
> qryCopyDayToPeriod.Databasename := qryProductPrice.DatabaseName;
> qryCopyDayToPeriod.SQL.Clear;
> qryCopyDayToPeriod.SQL.Add ('sp_dboption databasename, ''select
> into/bulkcopy'', TRUE');  // Disable inserts in the LOG
> qryCopyDayToPeriod.SQL.Add ('CopyDayToPeriod :OldProductNumber,
:SourceDate,
> :DestStartDate, :DestEndDate');
> qryCopyDayToPeriod.SQL.Add ('sp_dboption databasename, ''select
> into/bulkcopy'', FALSE'); // Enable inserts in the LOG
> qryCopyDayToPeriod.ParamByName('OldProductNumber').AsInteger :=
ProductNumber;
> qryCopyDayToPeriod.ParamByName('SourceDate').AsDate := SourceDate;
> qryCopyDayToPeriod.ParamByName('DestStartDate').AsDate := DestStartDate;
> qryCopyDayToPeriod.ParamByName('DestEndDate').AsDate := DestEndDate;
> qryCopyDayToPeriod.ExecSQL;
> qryCopyDayToPeriod.Free;

> Here I have 3 Stored Procedure calls in a row. Can it be done? Do I have
to
> write something inbetween or do I have to execute them one at a time?

> Jacob Pedersen

Re:Execute more than one Stored Procedure in one query


casper....@usa.net (Aleksey Emelyanov) wrote in
<88dneq$l...@bornews.borland.com>:

Quote
>In Interbase you can call SP from other SP.

That wasn't what I meant. :)

Anyone else know this?

Jacob Pedersen

Re:Execute more than one Stored Procedure in one query


Quote
Jacob Pedersen wrote in message

<8EDBA088Cjpedersentecho...@207.105.83.62>...

Quote
>Execute more than one Stored Procedure in one query. Can it be done?

An SQL statement may contain calls to stored procedures, but a TQuery can
only execute a single SQL statement, it cannot execute a script of
statements.

Quote
>qryCopyDayToPeriod.SQL.Add ('sp_dboption databasename, ''select
>into/bulkcopy'', TRUE');  // Disable inserts in the LOG

This isn't a valid SQL statement. Your best bet is to use a TStoredProc to
execute this, setting your two parameters using the Parameters property.

--
Wayne Niddery - WinWright Consulting
RADBooks - http://members.home.net/wniddery/
You have a Right to Free Speech, but not the right to make me listen, nor to
use my property as a soapbox.

Re:Execute more than one Stored Procedure in one query


To execute more than one stored proc you need to use "exec" and can not use
parameters. Pass the parameters directly in the statement like if you using
an ISQL.

Query.SQL.Add('exec proc1 "'+FormatDateTime('mm/dd/yyyy', Now)+'"');
Query.SQL.Add('exec proc2 '+IntToStr(n));

Obs.: Remember that BDE can only open a single dataset. If your procedures
returns datasets you can only execute one at a time.

Arthur

Quote
Jacob Pedersen wrote in message

<8EDBA088Cjpedersentecho...@207.105.83.62>...
Quote
>Execute more than one Stored Procedure in one query. Can it be done?

Re:Execute more than one Stored Procedure in one query


Hi Jacob!

On 15 Feb 2000 15:08:45 GMT, J.Peder...@techotel.dk (Jacob Pedersen)
wrote:

Quote
>Here I have 3 Stored Procedure calls in a row. Can it be done? Do I have to
>write something inbetween or do I have to execute them one at a time?

You have to put 'EXEC ' in front of your procedure names. With one
stored procedure it will work without it but every next has to have it
in front of name.

tomi.

Other Threads