title: What can cause the SQL Server JDBC error 'The value is not set for the parameter number 0' for an output parameter access?
body: <p>I have some Java code that accesses SQL Server 2005 which looks something like this:</p>
  
  <pre><code>CallableStatement cstmt = ...;
  ... // Set input parameters
  cstmt.registerOutParameter(11, Types.INTEGER);
  cstmt.execute();
  int out = cstmt.getInt(11);
  </code></pre>
  
  <p>And the following exception is thrown from the last line:</p>
  
  <pre><code>com.microsoft.sqlserver.jdbc.SQLServerException: The value is not set 
       for the parameter number 0.
     at com.microsoft.sqlserver.jdbc.SQLServerException.
       makeFromDriverError(Unknown Source)
     at com.microsoft.sqlserver.jdbc.SQLServerCallableStatement.
       skipOutParameters(Unknown Source)
     at com.microsoft.sqlserver.jdbc.SQLServerCallableStatement.
       getOutParameter(Unknown Source)
     at com.microsoft.sqlserver.jdbc.SQLServerCallableStatement.
       getterGetParam(Unknown Source)
     at com.microsoft.sqlserver.jdbc.SQLServerCallableStatement.
       getInt(Unknown Source)
     at org.jboss.resource.adapter.jdbc.WrappedCallableStatement.
       getInt(WrappedCallableStatement.java:192)
  </code></pre>
  
  <p>The stored procedure being called looks something like this:</p>
  
  <pre><code>CREATE PROCEDURE dbo.stored_proc ( -- 10 input parameters
                                   , @out_param INT OUTPUT) AS
  
  -- Variable declarations 
  
  SET @out_param = 0
  
  -- Do processing...
  
  SET @out_param = 1
  </code></pre>
  
  <p><strong>Since the output parameter is set to zero on entry to the stored procedure, under what circumstances could the value not be set? Or am I misinterpreting the error message?</strong></p>
  
  <p>This error is reproducible with:</p>
  
  <ul>
  <li>SQL Server JDBC Driver 1.2</li>
  <li>SQL Server 2005 (64-bit) service pack 2</li>
  <li>SQL Server 2005 (64-bit) service pack 3</li>
  </ul>
  
  <p><strong>Update:</strong> It seems to be occuring as a result of the <code>-- Do processing...</code> part of the stored procedure. Removing this eliminates the error. There's too much code to reproduce here, what I'd like is some pointers to possible causes to narrow down likely candidates.</p>
  
  <p><strong>Update:</strong> Injecting errors (e.g. divide by zero) into the <code>-- Do processing...</code> part of the stored procedure <em>does not</em> cause this exception to be thrown (instead, as expected, the <code>execute()</code> call fails with an appropriate error message).</p>
  
  <p><strong>Update:</strong> Decompiling the com.microsoft.sqlserver.jdbc.SQLServerCallableStatement class suggests that 'parameter number 0' is the stored procedure <em>return value</em>.</p>
  
  <p><strong>Update:</strong> I have been unable to reproduce this by calling the stored procedure directly through Management Studio.</p>
  
  <p><strong>Update:</strong> The ultimate cause of this error seems to be a <strong>deadlock in the stored procedure</strong>. Usually, however, deadlocks cause the <code>execute()</code> call to fail with a <code>SQLException</code> wrapping SQL Server error code 1205...</p>