Hi,
I am having great trouble getting a stored procedure output parameter to
return values to my Delphi app. I have searched DejaNews with no success
so assume (hope) I am not reiterating a well worn question - if I am,
could someone please direct me to some help.
The stored procedure is declared thus -
CREATE PROCEDURE rq_NavRSExtInv_Catl (@sDateFrom varchar(20), @sDateTo
varchar(20), @nBilled int OUTPUT) AS
The stored proc deletes some data in tables, updates others, calculates
@nBilled, and returns a result set.
Once compiled, using these commands in ISQLW it works fine
declare @nBilled int
exec rq_NavRSExtInv_Catl '21 apr 1998', '25 apr 1998', @nBilled OUTPUT
select @nBilled
However, when I try to access @nBilled (declared as a ptOutput
paramtype) from a Delphi app it is either 0 or garbage - it isnt being
set, but the result set is fine. The program is a simple one with no
form, it creates the objects at run time. I've also tried creating a
simple form with TDatabase, TStoredProc, setting the values etc. When
this is run it returns the result set but @nBilled is still not set.
This is the essential code of the program -
var @nBilled integer;
...
with TDataBase.Create(Application) do
try
DatabaseName := 'Test';
LoginPrompt := False;
DriverName := 'MSSQL';
Params.LoadFromFile('c:\fin130\RSDBParams.TXT');
Connected := True;
with TStoredProc.Create(Application) do
try
DatabaseName := 'Test';
StoredProcName := ParamStr(CPROCPARAM);
with Params do begin
with CreateParam(ftDateTime, '@sDateFrom', ptInput) do
AsString := ParamStr(CFROMPARAM);
with CreateParam(ftDateTime, '@sDateTo', ptInput) do
AsString := ParamStr(CTOPARAM);
CreateParam(ftInteger, '@nBilled', ptOutput)
end;
Open;
nBilled := ParamByName('@nBilled').Value;
if nBilled > 0 then begin
...
The SQL Statements being sent from the BDE to SQL Server are -
go
dbo.rq_NavRSExtInv_Catl;1 @sDateFrom="21 apr 1998", @sDateTo="25 apr
1998", @nBilled=NULL
go
The main differenct between these and those used in ISQLW is the lack of
'OUTPUT' after the @nBilled parameter and if this is omitted in ISQLW it
wont return a value either.
How do I get this to work? Is their some parameter of the TDatabase
driver that needs to be set for stored procs to return values? I believe
I need to get the BDE to append 'OUTPUT' after the output parameter for
it to work but cant find how to acheive this, I would have thought
declaring the parameter as ptOutput would be all you need. I've tried
using ptInputOutput, assigning a value like the input parameters, using
SQL Server ODBC driver rather than the MSSQL native driver but nothing
changes.
I desperately need this to work. Has anyone had experience with this
problem?? Your help would be gratefully appreciated.
Perry Cornish