🚀 go-pugleaf

Chris,

I assume that you are trying to return @Ref and your code should read
CREATE PROCEDURE GetLastSupplierRef
  @Ref INT
AS
SET NOCOUNT ON
  SELECT @Ref = MAX(Supplier_Ref)
    FROM TicSuppliers
IF (@Ref = NULL)
BEGIN
  SET @Ref = 0
END
RETURN @Ref //rather than RETURN 0
GO

Theoretically this should work if you have defined parameter 0 as
direction = pdReturnValue but I've always had trouble with return
parameters. What I have been able to get to work is to declare the
parameter as Output (both in the stored proc and in Builder)
try
CREATE PROCEDURE GetLastSupplierRef
  @Ref INT Output
AS
SET NOCOUNT ON
  SET @Ref = select(MAX(Supplier_Ref)
    FROM TicSuppliers)
IF (@Ref = NULL)
BEGIN
  SET @Ref = 0
END
GO

Assuming the storedproc name and param type and direction has been set
up in the IDE the Builder code is something like ...

int iLastRef ;
try
  {
    ADOStoredProc1->ExecProc();   //should use ExecProc as not
returning a selection
    iLastRef = ADOStoredProc1->Parameters->Items[0]->Value;
  }
  catch(EDatabaseError &E) // does not catch error
{
// how do you catch and decode the error?
    ShowMessage("Error executing GetLastSupplierRef. "+E.Message);
}

(I am using SQL Server 7, but assume that there are no relevant
differences here)


Chris Noble wrote in message <3acaea3b_1@dnews>...
>I am having problems in using a stored procedure in MS SQL 2000
(personal
>edition) using the Enterprise version Builder 5 with the latest
patches
>applied on a Windows 2000 workstation. I have installed MDAC 2.6 and
updated
>the help for ADO Express (which still seem to be out of date!)
>
>The ADO connection string works and I can access the data in the
tables.
>
>The stored procedure will look up the last supplier reference
(integer) and
>return its value or 0 if there are no entries in the column. I can
find ways
>round this by not using stored procedures but I would like to know
how do
>it. Please take note Borland!
>
>The stored procedure is listed at the end:
>
>I have failed to access the procedure and get OLE error 80040E 14
using
>either the object inspector (design time) or the following code (run
time):
>
>ADOStoredProc1->Close();
>ADOStoredProc1->ProcedureName = "GetLastSupplierRef";
>try
>  {
>    ADOStoredProc1->Open(); // also tried ExecProc()
>  }
>  catch(EDatabaseError &E) // does not catch error
>{
>// how do you catch and decode the error?
>}
>
>Are there any examples of how to use stored procedures with SQL 2000
using
>parameters and are there any updates to the documentation which
currently
>contains errors? eg ADOStoredProc1->SQL->Text = "ShowPurebreds";
there is no
>SQL property in TADOStoredPROC.
>Thanks for any help
>
>Chris Noble
>
>CREATE PROCEDURE GetLastSupplierRef
>  @Ref INT
>AS
>SET NOCOUNT ON
>  SELECT @Ref = MAX(Supplier_Ref)
>    FROM TicSuppliers
>IF (@Ref = NULL)
>BEGIN
>  SET @Ref = 0
>END
>RETURN 0
>GO
>
>
>
>