asked on

DBExpress - returning value from TSQLStoredProc

I set up a test as basic as possible and get the same problem, returning
value of 0 from a SQLStoredProc.

I did the following test:

1) Placed TSQLConnection on form
2) Placed TSQLStoredProc on form and connected it to SQLConnection and
identified the storedprocname
3) Procedure is (in MSSQL Server 2000):
INSERT REQHEAD (RequestNo) values (@requestno)
RETURN @@identity
(tried above with @id = @@identity as well as OUTPUT parameter)

4) Put button on form with code attached:
var v_id, v_id2 : integer;
begin
try
sqlstoredproc1.parambyname('@requestno').asstring := Edit1.text;
sqlstoredproc1.execproc;
v_id2 := sqlstoredproc1.parambyname('@RETURN_VALUE').asinteger;
except on E:Edatabaseerror do
showmessage(E.message);
end;
5) Tried same above with @id OUTPUT parameter as well

Both tests yield 0. Any ideas?

geobul

Hi,

You may try something like:

v_id2 := sqlstoredproc1.Params.Items[sqlstoredproc1.Params.Count - 1].asinteger;

because the result will become the last parameter (I hope).

Alternatively you can always get that value using a separate query in the terms of the same session ('select @@IDENTITY as Ident'), open it and get the value.

Regards, Geo

mokule

Hi,
it's just the opposite return value (from RETURN statement in stored procedure) is always number 0 parameter

ADOCmd: TADOCommand;
begin
with ADOCmd do
begin
CommandText := 'NowyDokument';
Parameters.Refresh;
Parameters.ParamByName('@symbol').Value := symbol;
Prepared := True;
Execute;
result := Parameters[0].Value;
end;
end;

DARRAH

ASKER

Sorry, regardless, I'm still getting 0 back. I'd prefer not to have to do a separate query when the stored proc should be able to return the value. I hate to think I'm doing something obviously wrong, and not picking up on that. But as I setup the simplest test, I don't see anything.

Any other ideas or tests?

DARRAH

ASKER

I tried move to dbexpsda.dll (Core Lab) and the problem is eliminated.

ASKER CERTIFIED SOLUTION

modulo

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial