Board index » delphi » HELP: Stored Procedure works with one but not the other

HELP: Stored Procedure works with one but not the other

Quote
>>The first works, not a problem.  The second, which I have used almost the
>>same code for does not.

Do the procs work when called from iSql or Enterprise Manager?

=Bill=

 

Re:HELP: Stored Procedure works with one but not the other


Hi:

I am trying to use a couple of stored procedures that are relatively
similar.

One accepts an Integer and returns an Integer.  The other accepts a String
and returns an Integer.

The first works, not a problem.  The second, which I have used almost the
same code for does not.
Below is the Delphi code:

  DM.spMemberLookup.Params[1].AsInteger := intCardNumber;
  DM.spMemberLookup.Prepare;
  DM.spMemberLookup.ExecProc;
  ShowMessage('MemberID = '
+IntToStr(DM.spMemberLookup.Params[0].AsInteger));
  ShowMessage(strAPN);
  DM.spProductLookup.Params[1].AsString := strAPN;
  DM.spProductLookup.Prepare;
  DM.spProductLookup.ExecProc;
  ShowMessage('ProductID = '
+IntToStr(DM.spProductLookup.Params[0].AsInteger));

Below are the stored procedures:

Create Procedure spMemberLookup @CardNo int
As
DECLARE @MemberID int

SELECT @MemberID = (SELECT MemberID
FROM Members
WHERE CardNo = @CardNo)
return(@MemberID)

And Also:

Create Procedure spProductLookup @APN varchar(16)
As
DECLARE @ProductID int

SELECT @ProductID = (SELECT ProductID
FROM Products
WHERE APN = @APN)
return (@ProductID)

When I execute the Delphi code I get the following error at the line:
  DM.spProductLookup.ExecProc;

Project Products.exe raised exception class EDBEngineError with message
'Operation not applicable.'.
Process stopped. Use Step or Run to continue.

Both SP are setup on TStoredProc objects and I have tried everything I can
think of.  Can someone see something I can't that is wrong with all the
above or know something I should try.

I am using Delphi 4 SP1, NT Workstation SP3 and MSSQL 6.5 SP4.

Thanks for your time and I will appreciated any ideas anyone may have.

Bye, Mark.

--
Mark Duregon
H & L Management Systems
www.halman.com.au
M...@halman.com.au

Re:HELP: Stored Procedure works with one but not the other


 >Do the procs work when called from iSql or Enterprise Manager?

Quote

Yep!  Just ran it from inside Visual InterDev and the Return value is set
and correct.

Mark.

Re:HELP: Stored Procedure works with one but not the other


Quote
>Create Procedure spProductLookup @APN varchar(16)
>As
>DECLARE @ProductID int

>SELECT @ProductID = (SELECT ProductID
>FROM Products
>WHERE APN = @APN)
>return (@ProductID)

I believe that return values must be integers.  If you want to pass a string
back, you must use an output parameter or return a result set.

Also, why not simply do:

SELECT @ProductID = ProductID
  FROM Products
  ...

-- Mark

Other Threads