none
SQL: Using Table Variable in Select Statement is identifying table as an undeclared scalar variable

    Question

  • I'm using SQL2008 R2. 

    1. I have declared a variable @Table1 which is populated with a result set returned from a stored procedure (usp_AA1stTierSubEarnMthToDate).  The USP runs fine and populates the @Table1 Table-type variable. 
    2. Where I'm running into issues is when I try to refer the @Table1 values in  a select statement.  I first JOIN @Table1 in the Select Statement then include 3 related fields in the Select Clause.  Between the Select Clause and the JOIN, @Table1 is referenced 5 times.  I'm getting an error for each of these 5 references that says "Must declare the scalar variable "@Table1".
    3. Also if I remove the @Table1 code from the Select Statement, everything runs fine. 

      Can anyone tell me where my code is messed up.  See code below:

    Thanks,

    ...bob sutor

    CODE:

    DECLARE @Table1 TABLE(YearMonth varchar(10), SLCo_SL_SLHDVendor_CertTyp varchar(30), SubCost numeric(15,2),
       OrigSubKAmt numeric(15,2), SubChangeAmt numeric(15,2), CurrSubKAmt numeric(15,2))

    INSERT @Table1(YearMonth, SLCo_SL_SLHDVendor_CertTyp, SubCost,
       OrigSubKAmt, SubChangeAmt, CurrSubKAmt)

    EXEC dbo.usp_AA1stTierSubEarnMthToDate

    SELECT
          MonthlyData.SLCo_SL_SLHDVendor_CertType
      
        , MonthlyData.JCCo
        , MonthlyData.SLCo
           , MonthlyData.SL
           , MonthlyData.FirstTierSubNum
           , MonthlyData.FirstTierSubName
           , MonthlyData.MthDateTime
           , MonthlyData.YearMonth
           , MonthlyData.AcctgMonth
           , MonthlyData.JobNum
           , MonthlyData.JobName
          
           --Monthly Changes in Values
           , MonthlyData.SubCost AS SubCostMonth
           , MonthlyData.OrigSubKAmt AS OrigSubKAmtMonth
           , MonthlyData.CurrSubKAmt AS CurrSubKAmtMonth
          
           --Cumulated values through end of month
           , @Table1.SubCost AS SubCostToDate
           , @Table1.OrigSubKAmt AS OrigSubKAmtToDate
           , @Table1.CurrSubKAmt AS CurrSubKAmtToDate
          
           , MonthlyData.JobStatusNum
           , MonthlyData.JobStatus
          
          
    FROM   udvAA1stTierSubEarnMonth AS MonthlyData

     LEFT JOIN @Table1 ON
       MonthlyData.SLCo_SL_SLHDVendor_CertType = @Table1.MonthlyData.SLCo_SL_SLHDVendor_CertType
      and MonthlyData.YearMonth = @Table1.YearMonth


    Bob Sutor


    • Edited by ConstPM Sunday, October 27, 2013 3:28 AM
    Sunday, October 27, 2013 3:27 AM

Answers

  • You need to supply an alias for the table variable and use that alias to qualify the column names.  So

    DECLARE @Table1 TABLE(YearMonth varchar(10), SLCo_SL_SLHDVendor_CertTyp varchar(30), SubCost numeric(15,2),
       OrigSubKAmt numeric(15,2), SubChangeAmt numeric(15,2), CurrSubKAmt numeric(15,2))
    
    INSERT @Table1(YearMonth, SLCo_SL_SLHDVendor_CertTyp, SubCost,
       OrigSubKAmt, SubChangeAmt, CurrSubKAmt)
    
    EXEC dbo.usp_AA1stTierSubEarnMthToDate
    
    SELECT
          MonthlyData.SLCo_SL_SLHDVendor_CertType
      
        , MonthlyData.JCCo
        , MonthlyData.SLCo
           , MonthlyData.SL
           , MonthlyData.FirstTierSubNum
           , MonthlyData.FirstTierSubName
           , MonthlyData.MthDateTime
           , MonthlyData.YearMonth
           , MonthlyData.AcctgMonth
           , MonthlyData.JobNum
           , MonthlyData.JobName
          
           --Monthly Changes in Values
           , MonthlyData.SubCost AS SubCostMonth
           , MonthlyData.OrigSubKAmt AS OrigSubKAmtMonth
           , MonthlyData.CurrSubKAmt AS CurrSubKAmtMonth
          
           --Cumulated values through end of month
           , t.SubCost AS SubCostToDate
           , t.OrigSubKAmt AS OrigSubKAmtToDate
           , t.CurrSubKAmt AS CurrSubKAmtToDate
          
           , MonthlyData.JobStatusNum
           , MonthlyData.JobStatus
          
          
    FROM   udvAA1stTierSubEarnMonth AS MonthlyData
    
     LEFT JOIN @Table1 AS t ON
       MonthlyData.SLCo_SL_SLHDVendor_CertType = t.MonthlyData.SLCo_SL_SLHDVendor_CertType
      and MonthlyData.YearMonth = t.YearMonth

    Sunday, October 27, 2013 4:17 AM

All replies

  • You can not refer table variable directly in sql 'select' statement. We need to use dynamic sql. Another alternate way is to use #table1 instead of @table1. In this case you can use directly in the 'select' statement becouse #table1 is physically exist.

    Refer below example,

    DECLARE @sql varchar(max)
    SET @sql = 'SELECT * FROM ' + @table1
    EXEC(@sql)


    Regards, RSingh

    Sunday, October 27, 2013 3:36 AM
  • You need to supply an alias for the table variable and use that alias to qualify the column names.  So

    DECLARE @Table1 TABLE(YearMonth varchar(10), SLCo_SL_SLHDVendor_CertTyp varchar(30), SubCost numeric(15,2),
       OrigSubKAmt numeric(15,2), SubChangeAmt numeric(15,2), CurrSubKAmt numeric(15,2))
    
    INSERT @Table1(YearMonth, SLCo_SL_SLHDVendor_CertTyp, SubCost,
       OrigSubKAmt, SubChangeAmt, CurrSubKAmt)
    
    EXEC dbo.usp_AA1stTierSubEarnMthToDate
    
    SELECT
          MonthlyData.SLCo_SL_SLHDVendor_CertType
      
        , MonthlyData.JCCo
        , MonthlyData.SLCo
           , MonthlyData.SL
           , MonthlyData.FirstTierSubNum
           , MonthlyData.FirstTierSubName
           , MonthlyData.MthDateTime
           , MonthlyData.YearMonth
           , MonthlyData.AcctgMonth
           , MonthlyData.JobNum
           , MonthlyData.JobName
          
           --Monthly Changes in Values
           , MonthlyData.SubCost AS SubCostMonth
           , MonthlyData.OrigSubKAmt AS OrigSubKAmtMonth
           , MonthlyData.CurrSubKAmt AS CurrSubKAmtMonth
          
           --Cumulated values through end of month
           , t.SubCost AS SubCostToDate
           , t.OrigSubKAmt AS OrigSubKAmtToDate
           , t.CurrSubKAmt AS CurrSubKAmtToDate
          
           , MonthlyData.JobStatusNum
           , MonthlyData.JobStatus
          
          
    FROM   udvAA1stTierSubEarnMonth AS MonthlyData
    
     LEFT JOIN @Table1 AS t ON
       MonthlyData.SLCo_SL_SLHDVendor_CertType = t.MonthlyData.SLCo_SL_SLHDVendor_CertType
      and MonthlyData.YearMonth = t.YearMonth

    Sunday, October 27, 2013 4:17 AM