Is there any other way to get the return value of a procedure?

[supremeljp]

Environment

• nanodbc version: latest
• DBMS name/version: sql server
• ODBC connection string: Driver={SQL Server};Uid=...;Pwd=...;Server=...;Database=...;
• OS and Compiler: win10, vs2019
• CMake settings:

Actual behavior

When executing a procedure, I can not get the return value if the query is like "{CALL MyProcedure}", rather than "declare @return int;exec @return=MyProcedure;select @return;"

Expected behavior

Is there any other way to get the return value of a procedure? I notice that there is a enum value, statement::PARAM_RETURN, can it be used in this case? If not, what is its usage?

Minimal Working Example

nanodbc::prepare(stmt, "declare @return int;exec @return=MultiResultsTest;select @return;");
auto result = nanodbc::execute(stmt);
int index = 0;
do
{
    std::cout << "result: " << ++index << std::endl;
    for (int i = 0; i < result.columns(); i++)
    {
      std::cout << result.column_name(i) << " ";
    }
    std::cout << std::endl;
    while (result.next())
    {
         for (int j = 0; j < result.rows(); j++)
     {
         std::cout << result.get<std::string>(j) << " ";
     }
     std::cout << std::endl;
    }
    std::cout << std::endl;
} while (result.next_result());

[mloskot]

Check the example here https://github.com/nanodbc/nanodbc/issues/216#issuecomment-499554884

[thebyohazard]

I'm working on figuring this out as well. I think what is meant here is a return value for a stored procedure, rather than a parameter for a stored procedure that is listed as output.

In MSSQL, you can use the return statement to return a value to the caller.

If you raiseerror (don't know about throw yet, as I'm on 2008) then the return value of your procedure is negative and depends on the argument you used. If you don't specify a return value and it succeeds, the return value is zero. This so thread summarizes that:

I've been using the other syntax for calling my procedures rather than execute, and I found this page that suggests using {? = CALL procname (?,?)} But when I bind the first question mark as an int, I get Invalid parameter type in the catch

Here's an example if it helps.

try {
        nanodbc::connection connection(get_connection_string(),5);
        nanodbc::statement statement(connection);
        statement.timeout(5);
        prepare(statement,"{? = CALL [test_nanodbc](?)};");
        int returnVal;
        statement.bind(0,&returnVal);
        char outParam[50];
        statement.bind_strings(1,outParam,(size_t)50,(size_t)1,nanodbc::statement::PARAM_OUT);
        nanodbc::result r = statement.execute();
        if(outParam!= "test") print(outParam);
        if(returnVal == 0) print("success");
        else print("Failure");
    }
    catch(const std::exception& e) {
        print("Could not call procedure %s", e.what());
    }

[thebyohazard]

Okay, I wasn't binding it as a return param, but the syntax using the question marks and binding works. Try something like this:

        nanodbc::connection connection(get_connection_string(),5);
        nanodbc::statement statement(connection);
        statement.timeout(5);

        prepare(statement,"{? = CALL [test_nanodbc](?)};");

        int returnVal;
        statement.bind(0,&returnVal,nanodbc::statement::PARAM_RETURN);
        char outParam[50];
        statement.bind_strings(1,outParam,(size_t)50,(size_t)1,nanodbc::statement::PARAM_OUT);

        try {
            nanodbc::result r = statement.execute();
        }
        catch(const std::exception& e){}

        print(returnVal);

Do note, however (for MSSQL Server 2008 at least) that if ANSI null warnings are produced by your stored procedure, then it messes with the return value of the stored procedure and will even prevent nanodbc from raising an exception. I'm trying to figure that out right now and I'll try to post back if I figure that out.