title: calling a stored procedure from C# using SqlDataAdapter
body: <p>I have a stored procedure which has been well tested and works perfectly from SQL Server Management Studio. All the procedure does is check for the existence of a record in a table, return it if it exists, or create it and then return it if it doesn't.</p>
  
  <p>The procedure looks like this:</p>
  
  <pre><code>CREATE proc [dbo].[spInsertSerialBatch] 
  
  @MOS_JOB varchar(12), --PASSED COMMAND LINE
  @MOS_LOT varchar(4) = NULL, --PASSED COMMAND LINE
  @MES_USER varchar(12) = 'NOT PASSED',--PASSED COMMAND LINE
  @COMPUTERNAME varchar(100) = 'NOT PASSED' --ENVIRONMENT VARIABLE
  
  as ....
  </code></pre>
  
  <p>I use a SqlDataAdapter, which I have used repeatedly without any problems. The setup looks like this:</p>
  
  <pre><code>    using (SqlCommand sqlComm = new SqlCommand("dbo.spInsertSerialBatch", serialBatchDataConnection))
                      {
                          if (serialBatchDataConnection.State != ConnectionState.Open)
                          {
                              serialBatchDataConnection.Open();
                          }
  
  
  
  sqlComm.CommandType = CommandType.StoredProcedure;
                      sqlComm.Parameters.AddWithValue("@MOS_JOB", options.jobNumber);
                      sqlComm.Parameters.AddWithValue("@MOS_LOT", options.lotNumber);
                      sqlComm.Parameters.AddWithValue("@MES_USER", options.userId);
                      sqlComm.Parameters.AddWithValue("@COMPUTERNAME", System.Environment.MachineName);
  
                      SqlDataAdapter sda = new SqlDataAdapter(sqlComm);
                      DataTable dt = new DataTable();
  
                      int rowsAffected = sda.Fill(dt);
  
  }
  </code></pre>
  
  <p>I then examine the results of the table after Fill is executed. It works fine when the row exists in the table, but if it doesn't, and the stored proc needs to generate it, Fill returns 0 rows and the data table remains empty. No errors/exceptions are thrown, I just get no results.</p>
  
  <p>I suppose I could change the code to use ExecuteNonQuery and not use the DataAdapter, but I see no reason why this shouldn't just work; I prefer having a data table (which may result in more than a single row in some cases) than using a data reader and looping over the data to get the results.</p>
  
  <p>Any suggestions as to why this might fail? I've looked over several posts on this and other sites that are similar, but haven't found a satisfactory answer. Any suggestions are greatly appreciated.</p>
  
  <p>Thanks,</p>
  
  <p>Gary</p>
  
  <p>The entrire sp is quite large and probably too proprietary to publish...</p>
  
  <pre><code>        --return inserted rows
          SELECT 'CREATED' as [spRESULT], o.* 
          FROM @output o 
  
          END         
  
  /*
      * Return existing SerialBatch(s)
      */
      BEGIN
  
          SELECT 'RETRIEVED' as [spRESULT], s.* 
          FROM SerialBatch s
          WHERE SerialBatchId = @formattedId
  
          UNION 
          /* 
          * pull in products that have components that need labels as well
          */
          SELECT 'RETRIEVED' as [spRESULT],s.* 
          FROM SerialBatch s
          WHERE SerialBatchParentId = @formattedId
      END 
  </code></pre>
  
  <p>This is the end of the stored procedure. I tried executing a DataReader instead and the result is the same...I get no results for the case when the sp has to create it, but again it runs perfectly stand-alone in SQL Server Management Studio. </p>