title: Invalid Column Name errors in dynamic query. Works in SQL Server 2008 but not in SQL Server 2012
body: <p>I'm in the middle of going from SQL Server 2008 (not R2) to SQL Server 2012. I've restored my database into SQL Server 2012 but I'm getting runtime errors trying to call a certain dynamic query. </p>
  
  <p>When I call this query from ASP, I'm getting a 500 error, and in the logs it says that I have an </p>
  
  <blockquote>
    <p>Invalid column name 'Vendor'.  </p>
  </blockquote>
  
  <p>When I run the same procedure directly in SSMS it tells me I have the following errors:</p>
  
  <blockquote>
    <p>Msg 207, Level 16, State 1, Line 46<br>
    Invalid column name 'Vendor'.</p>
    
    <p>Msg 207, Level 16, State 1, Line 46<br>
    Invalid column name 'Type'.  </p>
    
    <p>Msg 207, Level 16, State 1, Line 46<br>
    Invalid column name 'SubType'.</p>
    
    <p>Msg 207, Level 16, State 1, Line 46<br>
    Invalid column name 'CustPrice'.</p>
  </blockquote>
  
  <p>Here's the code in the stored procedure. The problem is only occurring when <code>@mode = 'Search'</code>:</p>
  
  <pre><code>ALTER PROCEDURE [dbo].[spProductSearch]
                  @idCompany      int = 0,
                  @idBusiness     int = 0,
                  @cat            int = 0,
                  @subCat         int = 0,
                  @idPerson       int = 0,
                  @target         nvarchar(99),
                  @targetSQL      nvarchar(99),
                  @priceLower     money = 0,
                  @priceUpper     money = 999999999.99,
                  @orderBy        nvarchar(9),
                  @grouping       int = 1,
                  @inStock        int = 0,
                  @isCurrent      int = 0,
                  @mode           nvarchar(99)
  
  AS 
  Set nocount on
  
  DECLARE         @sql            nvarchar(4000),                                
                  @paramlist      nvarchar(4000),
                  @orderLit       varchar(99),
                  @margin         decimal(9,5)
  
  
  Select  @margin = margin 
  From    Company 
  Where   idCompany = @idCompany              
  
  -- Determine the Select Statement
  
  If @mode = 'Consumables'
  Begin
      Select @sql = 
          'Select *
              FROM        dbo.PrinterConsumable pc 
              INNER JOIN  dbo.vwProductListAll p 
              ON          pc.consumableID = p.ProductID 
              WHERE       p.idCompany = @idCompany 
              AND         pc.printerID = @target
              AND         p.CustPrice BETWEEN @priceLower AND @priceUpper'
  End
  
  If @mode = 'Drill'
  Begin
      -- If drilling down through the Desktop subcat, show unique skus for vendorProductID and Supplier
      If @subcat = '1000010'
          Begin
              Select @sql = 
                  'Select *
                      FROM        dbo.vwProductListAll P      
                      INNER JOIN
                          (SELECT      vendorProductID, idSupplier, MIN(dealerBuy) AS minDealerBuy
                          FROM           dbo.Product
                          GROUP BY vendorProductID, idSupplier) PS 
                          ON PS.vendorProductID = P.ProductID AND PS.idSupplier = P.idSupplier AND PS.minDealerBuy = P.DealerPrice
                          WHERE       p.idCompany = @idCompany 
                          AND         p.CustPrice BETWEEN @priceLower AND @priceUpper'
          End
      Else
          Begin
              Select @sql = 
                  'Select *
                      FROM        dbo.vwProductListAll P      
                      WHERE       p.idCompany = @idCompany 
                      AND         p.CustPrice BETWEEN @priceLower AND @priceUpper'
          End
  End
  
  If @mode = 'Favs'
  Begin
      Select @sql = 
          'Select *
              FROM        dbo.vwProductListAll p
              INNER JOIN  dbo.ProductFavs pf ON p.idProduct = pf.idProduct
              WHERE       p.idCompany = @idCompany 
              AND         pf.idPerson = @idPerson
              AND         p.CustPrice BETWEEN @priceLower AND @priceUpper'
  End
  
  If @mode = 'Search'
  Begin
      Select @sql = 
              'SELECT B.businessName                                              As Vendor, 
                  PC.cat                                                          As Type, 
                  PSC.subCat                                                      As SubType,   
                  P.idProduct,
                  P.vendorProductID                                               As ProductID, 
                  P.description                                                   As ProductDesc, 
                  100 * dbo.fnCalcMargin(P.margin, PSC.margin, PC.margin, @margin) As Margin,
                  ROUND(CAST(P.dealerBuy + P.dealerBuy * dbo.fnCalcMargin(P.margin, PSC.margin, PC.margin, @margin) AS money), 2) AS CustPrice, 
                  CAST(P.rrp - (P.dealerBuy + P.dealerBuy * dbo.fnCalcMargin(P.margin, PSC.margin, PC.margin, @margin)) AS money) AS Saving,
                  P.dealerBuy                                                     AS DealerPrice, 
                  P.rrp,
                  B.idBusiness,
                  PC.idCat,
                  PSC.idSubCat,
                  Case IsNull(P.url, '''')
                  When '''' Then 0 Else 1 End as hasLink, 
                  Case IsNull(P.imgURL, '''')                 
                  When '''' Then 0 Else 1 End as hasImage, 
                  Case IsNull(PI.productInfo, '''')
                  When '''' Then 0 Else 1 End as hasInfo, 
                  Case IsNull(PI.overview, '''')
                  When '''' Then 0 Else 1 End as hasOverView, 
                  Case IsNull(PI.keyFeatures, '''')
                  When '''' Then 0 Else 1 End as hasKeyFeatures, 
                  Case IsNull(PI.warrantyInfo, '''')
                  When '''' Then 0 Else 1 End as hasWty, 
                  P.idSupplier, S.supplierName, S.location, P.inStock, p.due, p.isCurrent,
                  P.imgURL, P.imgURL2, P.url, P.pdfLink, P.isBulkFreight, P.isDoubleFreight, P.isImported
              FROM            dbo.Business B      
              INNER JOIN      dbo.Product P           ON B.idBusiness = P.idBusiness
              LEFT OUTER JOIN dbo.ProductInfo PI      ON P.idProduct = PI.idProduct
              INNER JOIN      dbo.Supplier S          ON P.idSupplier = S.idSupplier
              INNER JOIN      dbo.ProductSubCat PSC   ON P.idSubCat = PSC.idSubCat
              INNER JOIN      dbo.ProductCat PC       ON PSC.idCat = PC.idCat
              WHERE           P.idCompany = @idCompany 
              AND             (ROUND(CAST(P.dealerBuy + P.dealerBuy * dbo.fnCalcMargin(P.margin, PSC.margin, PC.margin, @margin) AS money), 2) BETWEEN @priceLower AND @priceUpper)   
              AND             ((p.idProduct = @target) OR (P.vendorProductID = @target) OR (CONTAINS(p.description, @targetSQL)))' 
  End
  
  -- Determine Filter
  If @mode = 'Drill'
  Begin
      If @idBusiness &gt; 0  
          Select @sql = @sql + '  AND idBusiness = @idBusiness '
  
      If @cat &gt; 0  
          Select @sql = @sql + '  AND idCat = @cat '
  
      If @subCat &gt; 0  
          Select @sql = @sql + '  AND idSubCat = @subCat '
  End
  
  If @inStock = 1
      Select @sql = @sql + ' AND p.instock &gt; 0'
  
  If @isCurrent = 1
      Select @sql = @sql + ' AND p.isCurrent = 1'
  
  -- Determine Sorting
  If @orderBy = 'Desc'
      Select @orderLit = 'p.ProductDesc'
  Else if @orderBy = 'Price'  
      Select @orderLit = 'p.CustPrice'
  Else
      Select @orderLit = 'p.ProductID'
  
  -- Determine Grouping
  If @grouping = 1
      Select @sql = @sql + ' ORDER BY p.Vendor, p.Type, p.SubType, ' + @orderLit
  Else
      Select @sql = @sql + ' ORDER BY ' + @orderLit
  
  -- Recompile hint to see if it resolves the query timeout issue.  It didn't!
  -- Select @sql = @sql + ' OPTION (RECOMPILE);'
  
  -- Setup the Parameter List 
  Select @paramList = '@idCompany     int,
                       @idBusiness    int,
                       @cat           int,
                       @subCat        int,
                       @target        nvarchar(99),   
                       @targetSQL     nvarchar(99),
                       @priceLower    money,
                       @priceUpper    money = 999999999.99,
                       @margin        decimal(9,5),
                       @idPerson      int'    
  
  -- Execute the query
  EXEC sp_executesql @sql, @paramlist, @idCompany, @idBusiness, @cat, @subCat, @target, @targetSQL, @priceLower, @priceUpper, @margin, @idPerson
  
  IF @@ERROR &lt;&gt; 0 
      RETURN (1)  
  
   --return success code (0)
  RETURN (0)
  </code></pre>
  
  <p>Not sure if this is relevant or not, but this code was originally written in SQL Server 2000. It's working okay in my SQL Server 2008 instance. I changed the compatibility level of the DB from 80 to 100 when I imported it into 2012.</p>
  
  <p>Any help would be much appreciated.</p>
  
  <p>Thanks,
  Mike. </p>