title: SQL Server Stored Procedure via SOCI/ODBC in C++
body: <p>I am using the <a href="http://soci.sourceforge.net/index.html" rel="nofollow">SOCI library</a> to programmatically interact with a SQL Server DB via ODBC. I am having trouble getting values via output parameters in stored procedures. Some code (modeled after <a href="http://soci.sourceforge.net/doc/3.2/statements.html#procedures" rel="nofollow">SOCI documentation</a>)...</p>
  
  <pre><code>/*
   * create procedure
   * Dan.soci_proc @id int, @name varchar(32) output
   * as begin
   * set nocount on;
   * select @name = name from Dan.soci_test where id = @id
   * end
   */      
  std::string sql = "Dan.soci_proc :id, :name";
  std::string name;
  int proc_ndx = 1;
  soci::procedure proc = (my_soci.get_session().prepare &lt;&lt; sql, soci::use(proc_ndx),
                                                             soci::use(name));
  std::cout &lt;&lt; "\nAttempting to execute stored procedure " &lt;&lt; size &lt;&lt; " times."
            &lt;&lt; std::endl;
  for (; proc_ndx &lt; adj_size; ++proc_ndx) {
    try { 
      proc.execute();
      while (proc.fetch())
        std::cout &lt;&lt; "Fetched: " &lt;&lt; name &lt;&lt; std::endl;
    } catch (const soci::odbc_soci_error&amp; e) {
      std::cerr &lt;&lt; "Error executing stored procedure." &lt;&lt; std::endl;
      std::cerr &lt;&lt; e.what() &lt;&lt; std::endl;
      std::cerr &lt;&lt; e.odbc_error_message() &lt;&lt; std::endl;
      return;
    }
  }
  </code></pre>
  
  <p>My code does not throw any errors or exceptions, but nothing is fetched, either. I have tried calling it many different ways (normal <code>exec</code> syntax, ODBC <code>call</code> syntax, etc.), but nothing seems to work. I'm wondering if the error goes back to <a href="http://msdn.microsoft.com/en-us/library/ms131685.aspx" rel="nofollow">this from here</a>...</p>
  
  <blockquote>
    <p>If an input/output parameter is omitted, or if a literal is supplied
    for the parameter, the driver discards the output value.</p>
  </blockquote>
  
  <p>Unfortunately, SOCI doesn't really seem to support parameter markers, at least as far as I can tell.</p>
  
  <p>I have made the code work by using this kind of syntax...</p>
  
  <pre><code>std::string sql = "declare @name varchar(32); "
                    "exec Dan.soci_proc :id, @name = @name output; select @name";
  ...
  soci::procedure proc = (my_soci.get_session().prepare &lt;&lt; sql, soci::use(proc_ndx),
                                                           soci::into(name));
  </code></pre>
  
  <p>But that isn't ideal, for reasons that I think are obvious.</p>
  
  <p>Has anyone out there used SOCI and have some input into what I need to do differently to get this to work?</p>
  
  <p>EDIT: I received the following response from the soci-users forum/mailing list...</p>
  
  <blockquote>
    <p>I haven't used SOCI for a couple of years but I did successfully get
    stored procedures to work with ODBC on SQL Server, end of 2011, soci
    version 2 (I think).</p>
    
    <p>I remember that to do it I had to use statement rather than procedure.</p>
    
    <p>I had to amend the backend to call SQLMoreResults in a loop.</p>
    
    <p>Output parameters I think were not supported in SOCI and I handled
    them direct with ODBC over which I also had a C++ layer.</p>
  </blockquote>
  
  <p>However, since I'm a recent college grad (just started my first job this month!) and relatively inexperienced with C++ and database applications, I would really appreciate as much help as people are able to offer! For instance, regarding the above response - the last two points are not exactly clear to me.</p>