From: hugo@perFact.REMOVETHIS.info.INVALID   
      
   On Mon, 18 Oct 2010 11:18:44 -0700, tshad wrote:   
      
   (snip)   
   >I also tried to use a temporary variable and changed all the reference form   
   >#table to @table (as well as change the declaration of the table) and it   
   >works fine if I don't have the where clause, but the where clause:   
   >   
   >WHERE (@sRecordStatus = 'ALL') OR ((@sRecordStatus <> 'ALL') AND (dStart =   
   >(Select MAX(dStart)   
   >     from @tfs   
   >     WHERE  @tfs.Retailer = Retailer and   
   >         @tfs.StoreID = StoreID and   
   >         @tfs.QuestionID = QuestionID)))   
   >   
   >I get the error:   
   >   
   >Must declare the scalar variable "@tfs"   
   >   
   >Does that mean you can't use temporary variables in a sub query???   
      
   Hi Tom,   
      
   You can use table variables everywhere in a query where you could use a   
   table. The problem you get is caused by qualifying column names with the   
   table name (e.g. @tfs.StoreID). The parser sees an @ sign where it   
   expects a scalar, and assumes a "normal" variable. To make sure the   
   parser interprets it as a qualified column, you'll have to alias the   
   table variable, e.g.:   
      
   WHERE (@sRecordStatus = 'ALL') OR ((@sRecordStatus <> 'ALL')   
    AND (dStart =   
   (Select MAX(dStart)   
        from @tfs AS Inner   
        WHERE  Inner.Retailer = Outer.Retailer and   
            Inner.StoreID = Outer.StoreID and   
            Inner.QuestionID = Outer.QuestionID)))   
      
   I have also added another alias for the reference to the table in the   
   outer query. You need that as well (even if you're not using a table   
   variable) - your code left these columns unqualified, which would have   
   caused SQL Server to first try to resolve them in the tables referenced   
   in the inner query, and only move to the outer query if that fails.   
      
   --   
   Hugo Kornelis, SQL Server MVP   
   My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis   
      
   --- SoupGate-Win32 v1.05   
    * Origin: you cannot sedate... all the things you hate (1:229/2)