Announcement

Collapse
No announcement yet.

Return value from stored procedure, how?

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • Return value from stored procedure, how?

    Hi
    How would I get a value like:

    RETURN @@ERROR

    From a stored procedure?

    Not working...

    SQL_FetchResult(1,1, %NEXT_ROW)
    MSGBOX SQL_ResultColumnString(1,1,1)

    Code:
    [77233.746] >SQL_Statement|DB 1|STMT 1|IMME|SQL: "EXEC INSERT_NEW_USER 'asdf','adfs'">
    [77233.746] | |DB 1 is OPEN
    [77233.746] | IMMEDIATE EXECUTION...
    [77233.746] | @CIn
    [77233.746] | >SQL_OpenStatement|DB 1|STMT 1>
    [77233.746] | | |STMT 1 is currently CLOSED
    [77233.746] | | apiAHSr=SQL_SUCCESS
    [77233.746] | | |DB 1|STMT 1 is now OPEN
    [77233.746] | <SQL_SUCCESS<
    [77233.751] | apiDIRr=SQL_SUCCESS
    [77233.751] | >SQL_AutoBindColumn|DB 1|STMT 1|COL ALL>
    [77233.751] | | Autobinding ALL columns
    [77233.751] | | >SQL_ResultColumnCount|DB 1|STMT 1>
    [77233.751] | | | apiNRCr=SQL_SUCCESS
    [77233.751] | | <0<
    [77233.751] | | ALL columns have been bound
    [77233.751] | <SQL_SUCCESS<
    [77233.751] <SQL_SUCCESS<
    
    [77233.751] >SQL_FetchResult|DB 1|STMT 1|ROW 0>
    [77233.751] | FetchType 1
    [77233.751] | Fetch NEXT Row
    [77233.751] | apiFSCr=SQL_ERROR
    [77233.751] <SQL_ERROR<
    [77233.751] @HAR
    [77233.751] @CIn
    [77233.751] apiGDRr=SQL_SUCCESS
    [77233.751] @GDM [Microsoft][ODBC SQL Server Driver]Invalid cursor state
    [77233.751] @Err
    [77233.751] ERROR: 1|1|-1|SQL_FetchResult|999999999|24000|0|[Microsoft][ODBC SQL Server Driver]Invalid cursor state|77233.751|
    [77233.751] |@OP$-60 ""
    [77233.751] apiGDRr=SQL_NO_DATA
    [77233.751] @GDM

  • #2

    [77233.751] >SQL_FetchResult|DB 1|STMT 1|ROW 0>
    [77233.751] | FetchType 1
    [77233.751] | Fetch NEXT Row
    [77233.751] | apiFSCr=SQL_ERROR
    [77233.751] <SQL_ERROR<
    Suggests that your SP is not returning anything. have you confirmed that it works as expected and does return a value?
    What's the DBMS??



    Just had another look at the start of the dump. Are you sure you should be using EXEC?

    Comment


    • #3
      This may be relevant:

      https://docs.microsoft.com/en-us/sql...l-server-ver15

      If an application submits a procedure using the Transact-SQL EXECUTE syntax (as opposed to the ODBC CALL escape sequence), the SQL Server ODBC driver passes the procedure call to SQL Server as a SQL statement rather than as an RPC. Also, output parameters are not returned if the Transact-SQL EXECUTE statement is used.


      Note also that the RETURN value from a SP is not the same as a result set generated by a SP.
      See CALL in SQLTools Help and the [?]= call .....

      Comment


      • #4
        MS SQL Server

        Thanks Stuart, I will look into that...

        Comment


        • #5
          I added this "SET NOCOUNT ON;" then everything worked.

          Comment


          • #6
            Originally posted by David Clarke View Post
            I added this "SET NOCOUNT ON;" then everything worked.
            That rings a quiet bell somewhere in the dim and distant memory*

            *Back when I was doing a lot of work with complex SQL Server SPs. I haven't done much in that area for a few years. It's more MySQL these days.

            Comment


            • #7
              I had some issues with that [return values] but I found the workaround of declaring an extra I-O parameter in the procedure header and setting it in the procedure and reading it on return.

              I also had some success defining a stored FUNCTION rather than a stored PROCEDURE .. but that was with Oracle and AFAIK that is NOT universal.

              I see you have solved your current issue, but one of these might come in handy some day.
              Michael Mattias
              Tal Systems Inc. (retired)
              Racine WI USA
              [email protected]
              http://www.talsystems.com

              Comment


              • #8
                This is from the ODBC API help file; SQLTools has counterpart calls for everything (and then some to really simplify things) but you will have to find them. I am not an SQLTools user.


                A procedure is an executable object stored on the data source. Generally, it is one or more SQL statements that have been precompiled. The escape sequence for calling a procedure is:

                {[?=]call procedure-name[([parameter][,[parameter]]...)]}

                where procedure-name specifies the name of a procedure and parameter specifies a procedure parameter.
                For more information on the procedure call escape sequence, see “Procedure Call Escape Sequence” in Appendix C, “SQL Grammar.”
                A procedure can have zero or more parameters. It can also return a value, as indicated by the optional parameter marker ?= at the start of the syntax. If parameter is an input or an input/output parameter, it can be a literal or a parameter marker. However, interoperable applications should always use parameter markers, as some data sources do not accept literal parameter values. If parameter is an output parameter, it must be a parameter marker. Parameter markers must be bound with SQLBindParameter before the procedure call statement is executed.
                FWIW, code not shown (how you are executing the procedure).
                Michael Mattias
                Tal Systems Inc. (retired)
                Racine WI USA
                [email protected]
                http://www.talsystems.com

                Comment


                • #9
                  At the moment I return values by doing SELECT THE_ANSWER etc... It works fine but requires me to move to the first record etc. Thought a real return value might just show up. Not a big deal.
                  Thanks!

                  Comment


                  • #10
                    Well, a procedure 'can' return a recordset which can be FETCHED row-by-row but that is not returned as a 'return code'...and I have never seen that using ODBC except for some of the ODBC catalog functions.

                    Using OLE with ADO you end up returning such a recordset to a variant or to an object variable.

                    I think the bottom line here is, "procedure header not shown"


















                    Michael Mattias
                    Tal Systems Inc. (retired)
                    Racine WI USA
                    [email protected]
                    http://www.talsystems.com

                    Comment

                    Working...
                    X