DBExpress - returning value from TSQLStoredProc

Posted on 2004-11-17
Last Modified: 2010-04-16
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;
      sqlstoredproc1.parambyname('@requestno').asstring := Edit1.text;
      v_id2 := sqlstoredproc1.parambyname('@RETURN_VALUE').asinteger;
     except on E:Edatabaseerror do
5) Tried same above with @id OUTPUT parameter as well

Both tests yield 0. Any ideas?
Question by:DARRAH
    LVL 17

    Expert Comment


    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
    LVL 17

    Expert Comment

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

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

    Author Comment

    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?

    Author Comment

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

    Accepted Solution

    Closed, 250 points refunded.

    Community Support Moderator
    Experts Exchange

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    IT, Stop Being Called Into Every Meeting

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Introduction The parallel port is a very commonly known port, it was widely used to connect a printer to the PC, if you look at the back of your computer, for those who don't have newer computers, there will be a port with 25 pins and a small print…
    In this tutorial I will show you how to use the Windows Speech API in Delphi. I will only cover basic functions such as text to speech and controlling the speed of the speech. SAPI Installation First you need to install the SAPI type library, th…
    Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
    Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

    759 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    13 Experts available now in Live!

    Get 1:1 Help Now