title: Calling Stored Procedure w/ Output Parameter within Stored Procedure - Invalid Column Name
body: <p>I have written a stored procedure that uses an output variable. The stored procedure runs fine, I know that for a fact. I can run the stored procedure using the snippet below</p>
  
  <pre><code>DECLARE @return_value int,
      @coEfficientAlpha float
  
  EXEC    @return_value = [dbo].[getTestCoEfficientAlphaByDasIdAndKeyStandard]
      @dasId = 4001,
      @keyStandardId = 960,
      @coEfficientAlpha = @coEfficientAlpha OUTPUT
  
  SELECT  @coEfficientAlpha as N'@coEfficientAlpha'
  
  SELECT  'Return Value' = @return_value
  
  GO
  </code></pre>
  
  <p>but when I call this proc from within my other stored procedure, <code>getTestReliabilityOnDasId</code>, I receive an </p>
  
  <blockquote>
    <p>Invalid Column Name</p>
  </blockquote>
  
  <p>error message. I'm not doing anything strange within either procedure. Any ideas?</p>
  
  <p>I'll post more code if needed.</p>
  
  <p>Edit:</p>
  
  <p>My code for the 2 stored procedures is below:</p>
  
  <p>Ok, the definition of <code>getTestCoEfficientAlphaByDasIdAndKeyStandard</code> is </p>
  
  <pre><code>ALTER procedure [dbo].[getTestCoEfficientAlphaByDasIdAndKeyStandard]
  @dasId int,
  @keyStandardId int,
  @coEfficientAlpha float output
  as
  
  --declare @dasId int
  --set @dasId = 4001
  
  --declare @keyStandardId int
  --set @keyStandardId = 960
  
  -- used all over query
  select qa.*, dq.dasQuestionId
  into #temp
  from test t 
      inner join question q on t.testId = q.testId
      inner join questionAnswer qa on q.questionId = qa.questionId
      inner join dasQuestion dq on t.dasId = dq.dasId and q.questionNumber = dq.questionNumber
      inner join keyStandardDasQuestion ksdq on dq.dasQuestionId = ksdq.dasQuestionId
  where t.dasId = @dasId
      and q.questionTypeId = 2
      and ksdq.keyStandardId = @keyStandardId
  
  
  -- used later in query
  select tb1.DasQuestionId, sum(square(tb1.score - tb2.avgScore))/count(tb1.score) as qSd2
  into #questions
  from #temp tb1
      inner join (
          select questionId, sum(score)/cast(count(score) as float) as avgScore
          from #temp
          group by questionId
      )tb2 on tb1.questionId = tb2.questionId
  group by tb1.dasQuestionId
  
  -- used later in query
  select studentNo, sum(score) as studentTestScore, count(score) as cntTestScore
  into #testData
  from #temp
  group by studentNo
  
  -- average number of questions correct on test          
  declare @avgTScore float
  set @avgTScore = (  
      select sum(studentTestScore) / cast(count(studentTestScore) as float) from     #testData    
      )   
  
  -- average variance of questions right on test
  declare @tSd2 float
  set @tSd2 = (
          select sum(xMinusMean)/count(xMinusMean) as tSd2
          from (
              select (t.studentTestScore - @avgTScore)*(t.studentTestScore - @avgTScore) as xMinusMean
          from #testData t
          )tb1 
  
      )
  
  set @coEfficientAlpha = (
      select (count(distinct dasQuestionId)/ (cast(count(distinct dasQuestionId) as     float) - 1))*(1-((select sum(qSd2) from #questions)/@tSd2)) as coEfficientAlpha
      from #temp
  )
  
  drop table #temp, #questions , #testdata
  </code></pre>
  
  <p>and the definition of <code>getTestReliabilityOnDasId</code> is </p>
  
  <pre><code>ALTER procedure [dbo].[getTestReliabilityOnDasId]
  @dasId int
  as
  
  
  --declare @dasId int
  --set @dasId = 4001
  
  select ROW_NUMBER() OVER(order by ks.keyStandardId asc) AS rowNumber,
      da.dasId, da.title, ks.keyStandardId, ks.keyStandardText, count(dq.dasQuestionId)     as countQuestions
  into #temp
  from districtAssessment da
      inner join dasQuestion dq on da.dasId = dq.dasId
      inner join keyStandardDasQuestion ksdq on dq.dasQuestionId = ksdq.dasQuestionId
      inner join keyStandard ks on ksdq.keyStandardId = ks.keyStandardId
  where da.dasId = @dasId
  group by da.dasId, da.title, ks.keyStandardId, ks.keyStandardText
  
  
  create table #KeyStandards(
      [keyStandardId] int,
      coEfficientAlpha float
  )
  
  declare @numRows int
  select @numRows = max(rowNumber)
  from #temp
  
  declare @keyStandardId int, @rowNumber int
  set @rowNumber = 1
  
  DECLARE @return_value int,
          @coEfficientAlpha float,
          @numQuestions int
  
  WHILE @rowNumber &lt;= @numRows
  BEGIN
      set @keyStandardId = (
     select keyStandardId
     from #temp
     where rowNumber = @rowNumber )
  
      set @numQuestions = (select countQuestions from #temp where keyStandardId = @keyStandardId   )
  
      if @numQuestions &gt; 1
      begin
          EXEC    @return_value = [dbo].    [getTestCoEfficientAlphaByDasIdAndKeyStandard]
                  @dasId = @dasId,
                  @keyStandardId = @keyStandardId,
              @coEfficientAlpha = @coEfficientAlpha OUTPUT
  
      end
  
     insert into #keyStandards
     select @keyStandardId, case when @coEfficientAlpha is null then 'Not Enough     Questions' else @coEfficientAlpha end 
  
     set @rowNumber = @rowNumber + 1
  END
  
  select t.*, k.coEfficientAlpha
  from #temp t
      inner join #keyStandards k on t.keyStandardId = k.keyStandardId
  
  drop table #temp, #KeyStandards
  </code></pre>
  
  <p>The exact error message I am getting is:</p>
  
  <blockquote>
    <p>Msg 207, Level 16, State 1, Procedure getTestCoEfficientAlphaByDasIdAndKeyStandard, Line 33<br>
    Invalid column name 'questionId'.<br>
    Msg 207, Level 16, State 1, Procedure getTestCoEfficientAlphaByDasIdAndKeyStandard, Line 31<br>
    Invalid column name 'questionId'.<br>
    Msg 207, Level 16, State 1, Procedure getTestCoEfficientAlphaByDasIdAndKeyStandard, Line 31<br>
    Invalid column name 'score'.<br>
    Msg 207, Level 16, State 1, Procedure getTestCoEfficientAlphaByDasIdAndKeyStandard, Line 31<br>
    Invalid column name 'score'.<br>
    Msg 207, Level 16, State 1, Procedure getTestCoEfficientAlphaByDasIdAndKeyStandard, Line 34<br>
    Invalid column name 'questionId'.<br>
    Msg 207, Level 16, State 1, Procedure getTestCoEfficientAlphaByDasIdAndKeyStandard, Line 35<br>
    Invalid column name 'dasQuestionId'.<br>
    Msg 207, Level 16, State 1, Procedure getTestCoEfficientAlphaByDasIdAndKeyStandard, Line 27<br>
    Invalid column name 'DasQuestionId'.<br>
    Msg 207, Level 16, State 1, Procedure getTestCoEfficientAlphaByDasIdAndKeyStandard, Line 27<br>
    Invalid column name 'score'.<br>
    Msg 207, Level 16, State 1, Procedure getTestCoEfficientAlphaByDasIdAndKeyStandard, Line 27<br>
    Invalid column name 'score'.<br>
    Msg 207, Level 16, State 1, Procedure getTestCoEfficientAlphaByDasIdAndKeyStandard, Line 41<br>
    Invalid column name 'studentNo'.<br>
    Msg 207, Level 16, State 1, Procedure getTestCoEfficientAlphaByDasIdAndKeyStandard, Line 38<br>
    Invalid column name 'studentNo'.<br>
    Msg 207, Level 16, State 1, Procedure getTestCoEfficientAlphaByDasIdAndKeyStandard, Line 38<br>
    Invalid column name 'score'.<br>
    Msg 207, Level 16, State 1, Procedure getTestCoEfficientAlphaByDasIdAndKeyStandard, Line 38<br>
    Invalid column name 'score'.<br>
    Msg 207, Level 16, State 1, Procedure getTestCoEfficientAlphaByDasIdAndKeyStandard, Line 66<br>
    Invalid column name 'dasQuestionId'.<br>
    Msg 207, Level 16, State 1, Procedure getTestCoEfficientAlphaByDasIdAndKeyStandard, Line 66<br>
    Invalid column name 'dasQuestionId'.  </p>
  </blockquote>
  
  <p>Note: If I run <code>getTestReliabilityOnDasId</code> I get these error messages.  If I run <code>getTestCoEfficientAlphaByDasIdAndKeyStandard</code> and then run <code>getTestReliabilityOnDasId</code> in a separate query window, then everything runs fine. It's almost like the SQL Server engine has to warm up or something...</p>