title: Output parameter from Stored Procedure only returning zero
body: <p><strong>System background:</strong> Coding in VBA using MS-Access 2010.  Currently working on code behind module and calling stored procedure. The stored procedure is written in SQL and run on the Ms-SQL server 2008 application where the database is stored.  </p>
  
  <p><strong>Stored Procedure:</strong> The stored procedure's purpose is to:</p>
  
  <ul>
  <li>Retrieve three input parameters: WOID, SampleID and Analyte</li>
  <li>Join two tables: tblWoSampleTest , tblTest</li>
  <li>Select testID WHERE the three values match </li>
  </ul>
  
  <p><em>note:</em> WOID and SampleID column are in tblWoSampleTest and Analyte is in tbltest</p>
  
  <p>Once the stored procedure is called, the testId is saved to a local variable ThisTestID</p>
  
  <pre><code>CREATE PROCEDURE upGetTestIDForAnalyte @WOID nvarchar(60), @SampleID nvarchar(60),@Analyte nvarchar(60), @TestId int OUT
  AS
  
     SELECT @TestID = (Select TestID = t1.TestID
     FROM tblWOSampleTest t1
     JOIN tblTest t2
     ON t1.TestID=t2.TestID 
     WHERE @WOID = t1.WOID AND @SampleID = t1.SampleID AND @Analyte = t2.Analyte)
  
  GO
  </code></pre>
  
  <p>My issue is every time I call the stored procedure, The value ThistestId was previously initialized to is returned even though I know the test Id exists and the stored procedure seemed to run correctly.  To verify it exists I took my stored procedure and simply ran:</p>
  
  <pre><code>Select TestID = t1.TestID
  FROM tblWOSampleTest t1
  JOIN tblTest t2
  ON t1.TestID=t2.TestID 
  WHERE @WOID = t1.WOID AND @SampleID = t1.SampleID AND @Analyte = t2.Analyte
  </code></pre>
  
  <p>and had the correct testId returned (there will only ever be one value).  I don't think there is an issue with the data type because the testid is a number not a string.  Also here is the way I call it, although I am pretty sure this method is correct.</p>
  
  <pre><code>ThisTestId = 5
  Set Conn = New ADODB.connection
  
  
  Conn.ConnectionString = "connection string"
  Conn.Open
  
  Set cmd = New ADODB.Command
  cmd.ActiveConnection = Conn
  cmd.CommandType = adCmdStoredProc
  cmd.CommandText = "upGetTestIDForAnalyte"
  
      cmd.Parameters.Append cmd.CreateParameter("@Analyte", adVarChar, adParamInput, 60, Analyte)
      cmd.Parameters.Append cmd.CreateParameter("@WOID", adVarChar, adParamInput, 60, ThisWOID)
      cmd.Parameters.Append cmd.CreateParameter("@SampleID", adVarChar, adParamInput, 60, 1)
      cmd.Parameters.Append cmd.CreateParameter("@testid", adDouble, adParamOutput, , ThisTestID)
  
  cmd.Execute
  Conn.Close
  msgbox ThisTestId
  </code></pre>
  
  <p>In this case a 5 will be printed</p>