Board index » delphi » Stored proc not returning values with output parameters

Stored proc not returning values with output parameters

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

 

Re:Stored proc not returning values with output parameters


Delphi's TStoredProc component will return the output parameter only when
you call ExecProc instead of Open method. Open method will return a dataset
but not the output parameter. The workaround is to make your @nBilled a
field in the returned dataset such as  Select *, MyReturnField = @nBilled
FROM  MyTable. MyReturnField will hold that value, and will be the same for
all records in the returned dataset. The problem is you have to make sure
the returned dataset is not empty, otherwise, the value can't be returned.

Quote
Perry Cornish wrote in message <35446D95.9DFED...@reiq.com.au>...
>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.

Other Threads