Invalid Column Name errors in dynamic query. Works in SQL Server 2008 but not in SQL Server 2012 -
i'm in middle of going sql server 2008 (not r2) sql server 2012. i've restored database sql server 2012 i'm getting runtime errors trying call dynamic query.
when call query asp, i'm getting 500 error, , in logs says have
invalid column name 'vendor'.
when run same procedure directly in ssms tells me have following errors:
msg 207, level 16, state 1, line 46
invalid column name 'vendor'.msg 207, level 16, state 1, line 46
invalid column name 'type'.msg 207, level 16, state 1, line 46
invalid column name 'subtype'.msg 207, level 16, state 1, line 46
invalid column name 'custprice'.
here's code in stored procedure. problem occurring when @mode = 'search':
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) set nocount on declare @sql nvarchar(4000), @paramlist nvarchar(4000), @orderlit varchar(99), @margin decimal(9,5) select @margin = margin company idcompany = @idcompany -- determine select statement if @mode = 'consumables' begin select @sql = 'select * dbo.printerconsumable pc inner join dbo.vwproductlistall p on pc.consumableid = p.productid p.idcompany = @idcompany , pc.printerid = @target , p.custprice between @pricelower , @priceupper' end if @mode = 'drill' begin -- if drilling down through desktop subcat, show unique skus vendorproductid , supplier if @subcat = '1000010' begin select @sql = 'select * dbo.vwproductlistall p inner join (select vendorproductid, idsupplier, min(dealerbuy) mindealerbuy dbo.product group vendorproductid, idsupplier) ps on ps.vendorproductid = p.productid , ps.idsupplier = p.idsupplier , ps.mindealerbuy = p.dealerprice p.idcompany = @idcompany , p.custprice between @pricelower , @priceupper' end else begin select @sql = 'select * dbo.vwproductlistall p p.idcompany = @idcompany , p.custprice between @pricelower , @priceupper' end end if @mode = 'favs' begin select @sql = 'select * dbo.vwproductlistall p inner join dbo.productfavs pf on p.idproduct = pf.idproduct p.idcompany = @idcompany , pf.idperson = @idperson , p.custprice between @pricelower , @priceupper' end if @mode = 'search' begin select @sql = 'select b.businessname vendor, pc.cat type, psc.subcat subtype, p.idproduct, p.vendorproductid productid, p.description productdesc, 100 * dbo.fncalcmargin(p.margin, psc.margin, pc.margin, @margin) margin, round(cast(p.dealerbuy + p.dealerbuy * dbo.fncalcmargin(p.margin, psc.margin, pc.margin, @margin) money), 2) custprice, cast(p.rrp - (p.dealerbuy + p.dealerbuy * dbo.fncalcmargin(p.margin, psc.margin, pc.margin, @margin)) money) saving, p.dealerbuy dealerprice, p.rrp, b.idbusiness, pc.idcat, psc.idsubcat, case isnull(p.url, '''') when '''' 0 else 1 end haslink, case isnull(p.imgurl, '''') when '''' 0 else 1 end hasimage, case isnull(pi.productinfo, '''') when '''' 0 else 1 end hasinfo, case isnull(pi.overview, '''') when '''' 0 else 1 end hasoverview, case isnull(pi.keyfeatures, '''') when '''' 0 else 1 end haskeyfeatures, case isnull(pi.warrantyinfo, '''') when '''' 0 else 1 end 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 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 p.idcompany = @idcompany , (round(cast(p.dealerbuy + p.dealerbuy * dbo.fncalcmargin(p.margin, psc.margin, pc.margin, @margin) money), 2) between @pricelower , @priceupper) , ((p.idproduct = @target) or (p.vendorproductid = @target) or (contains(p.description, @targetsql)))' end -- determine filter if @mode = 'drill' begin if @idbusiness > 0 select @sql = @sql + ' , idbusiness = @idbusiness ' if @cat > 0 select @sql = @sql + ' , idcat = @cat ' if @subcat > 0 select @sql = @sql + ' , idsubcat = @subcat ' end if @instock = 1 select @sql = @sql + ' , p.instock > 0' if @iscurrent = 1 select @sql = @sql + ' , 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 p.vendor, p.type, p.subtype, ' + @orderlit else select @sql = @sql + ' order ' + @orderlit -- recompile hint see if resolves query timeout issue. didn't! -- select @sql = @sql + ' option (recompile);' -- setup 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 query exec sp_executesql @sql, @paramlist, @idcompany, @idbusiness, @cat, @subcat, @target, @targetsql, @pricelower, @priceupper, @margin, @idperson if @@error <> 0 return (1) --return success code (0) return (0) not sure if relevant or not, code written in sql server 2000. it's working okay in sql server 2008 instance. changed compatibility level of db 80 100 when imported 2012.
any appreciated.
thanks, mike.
the problem turned out order part of query.
-- 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 p.vendor, p.type, p.subtype, ' + @orderlit else select @sql = @sql + ' order ' + @orderlit i removed p. infront of derived column names , query ran fine, ie:
-- determine sorting if @orderby = 'desc' select @orderlit = 'productdesc' else if @orderby = 'price' select @orderlit = 'custprice' else select @orderlit = 'productid' -- determine grouping if @grouping = 1 select @sql = @sql + ' order vendor, type, subtype, ' + @orderlit else select @sql = @sql + ' order ' + @orderlit funny how working okay in sql2000 - 2008.
Comments
Post a Comment