Why would a specific comparison return records from a SQL Server Stored Proc but an IN clause containing the same value return nothing? -
i have 2 stored procedures identical except 2 things:
(a) 1 works (returns records) , other doesn't (returns no records)
(b) working 1 searches single match, whereas nonworking 1 casts wider net, including additional values in being sought
the differences (besides sp names) between 2 stored procedures are:
1) working stored proc contains this:
where upper(up.unit) = 'rock bottom' ...the nonworking stored proc contains this:
where upper(up.unit) in ('chophouse', 'craftworks sc', 'gordon biersch', 'old chi franchise', 'old chicago', 'rock bottom') 2)
where working stored proc contains this:
where upper(up.unit) = 'rock bottom' , c.begindate <= @calendarenddate , c.enddate >= @calendarbegdate ...the nonworking stored proc contains this:
where upper(mp.unit) in ('chophouse', 'craftworks sc', 'gordon biersch', 'old chi franchise', 'old chicago', 'rock bottom') , c.begindate <= @calendarenddate , c.enddate >= @calendarbegdate 3)
and finally, working stored proc contains this:
and unit = ''rock bottom'' , ...the nonworking stored proc contains this:
and unit in (''chophouse'', ''craftworks sc'', ''gordon biersch'', ''old chi franchise'', ''old chicago'', ''rock bottom'') , why searching 'rock bottom' along other values fail, while looking value alone succeed?
update
at commenter kritner's request, here stored procs in entirety; first 1 one works:
if object_id ( 'sp_viewpricematrix_variance_rockbottomrollup_rockbottom', 'p' ) not null drop procedure sp_viewpricematrix_variance_rockbottomrollup_rockbottom; go create procedure [dbo].[sp_viewpricematrix_variance_rockbottomrollup_rockbottom] @begdate datetime, @enddate datetime, @sortby varchar(20) declare @sqlstring varchar(max), @statement varchar(8000), @shortname varchar(50), @itemcode varchar(25), @priceweek varchar(30), @lastpriceweek varchar(30), @week int, @wherepriceweek varchar(2000), @price varchar(25), @contractprice int, @calendarbegdate datetime, @calendarenddate datetime create table #temp ( unit varchar(50), shortname varchar(25), itemcode varchar(50), description varchar(250), regionorder int, contractprice varchar(50), price varchar(25), variance varchar(25), priceweek varchar(50), week varchar(10) ) -- create temp table select up.unit, mm.shortname, up.itemcode, description=(select description masterproducts itemcode=up.itemcode), mm.regionorder, up.contractprice #tempcontract unitproducts inner join unitmembers um on up.unit=um.unit , abs(um.pricesheet) = 1 inner join members mm on um.memberno = mm.memberno upper(up.unit) = 'rock bottom' select @calendarbegdate = c.begindate calendar c @begdate between c.begindate , c.enddate select @calendarenddate = c.enddate calendar c @enddate between c.begindate , c.enddate -- weeks , clause set @wherepriceweek = ' ' declare getpriceweek cursor select [priceweek] = c.description, c.begindate calendar c c.begindate <= @calendarenddate , c.enddate >= @calendarbegdate order 2 open getpriceweek fetch next getpriceweek @priceweek, @begdate while @@fetch_status = 0 begin select @statement = ('alter table #tempcontract add [' + @priceweek + '] numeric(8,2) ') exec (@statement) if(@wherepriceweek<>' ') begin set @wherepriceweek = @wherepriceweek + 'or ' end set @wherepriceweek = @wherepriceweek + 'isnull(['+@priceweek+'],''0.00'') <> ''999.99'' ' fetch next getpriceweek @priceweek, @begdate end close getpriceweek deallocate getpriceweek -- build member data weeks declare getmembercolumns cursor select distinct shortname,itemcode #tempcontract order shortname,itemcode open getmembercolumns fetch next getmembercolumns @shortname,@itemcode while @@fetch_status = 0 begin declare getmemberprice cursor select [priceweek] = c.description, convert(varchar(20), cast(isnull(mp.price,0) numeric(8,2))) price, up.contractprice memberprice mp inner join unitproducts on mp.unit=up.unit , mp.itemcode=up.itemcode inner join unitmembers um on mp.memberno=um.memberno , mp.unit=um.unit , abs (um.pricesheet) = 1 inner join members mm on mp.memberno = mm.memberno inner join calendar c on mp.cyear=c.cyear , mp.cweek=c.cweek upper(up.unit) = 'rock bottom' , c.begindate <= @calendarenddate , c.enddate >= @calendarbegdate , mm.shortname = @shortname , mp.itemcode = @itemcode open getmemberprice fetch next getmemberprice @priceweek,@price,@contractprice while @@fetch_status = 0 begin print(@price) select @statement = ('update #tempcontract set [' + @priceweek + ']=''' + isnull(@price,'0.00') + ''' itemcode=''' + @itemcode + ''' , unit = ''rock bottom'' , [shortname]=''' + @shortname +'''') exec (@statement) fetch next getmemberprice @priceweek,@price,@contractprice end close getmemberprice deallocate getmemberprice fetch next getmembercolumns @shortname,@itemcode end close getmembercolumns deallocate getmembercolumns --select * #tempcontract -- final select statement set @week = 0 set @lastpriceweek = '' set @sqlstring = '' declare setpriceweeksql cursor select [priceweek] = c.description, c.begindate calendar c c.begindate between @calendarbegdate , @calendarenddate order 2 open setpriceweeksql fetch next setpriceweeksql @priceweek, @begdate while @@fetch_status = 0 begin set @week = @week + 1 if(@sqlstring='') begin set @sqlstring = @sqlstring + 'insert #temp select unit, shortname, itemcode, description, regionorder, contractprice, isnull('+ '['+@priceweek+'],''0.00'') price, (convert(decimal(10,3),''-0.001'')) variance, '''+@priceweek+''' priceweek, ''wk'+convert(varchar(2),@week)+''' week #tempcontract'+@wherepriceweek if(@sortby='members') begin set @sqlstring = @sqlstring + ' union select unit, shortname, '''', ''zzzz'', '''', '''', ''0'' price, ''-0.001'' variance, '''' priceweek, ''wk'+convert(varchar (2),@week)+''' week #tempcontract'+@wherepriceweek end else begin set @sqlstring = @sqlstring + ' union select unit, '''', itemcode, description, ''1000'', contractprice, ''0'' price, ''-0.001'' variance, '''' priceweek, ''wk'+convert(varchar(2),@week)+''' week #tempcontract'+@wherepriceweek end end else begin set @sqlstring = @sqlstring + ' union ' set @sqlstring = @sqlstring + 'select unit, shortname, itemcode, description, regionorder, contractprice, isnull('+ '['+@priceweek+'],''0.00'') price, isnull(convert(decimal(10,2),['+@priceweek +'])-convert(decimal(10,2),['+@lastpriceweek+']),''0.00'') variance, '''+@priceweek+''' priceweek, ''wk'+convert(varchar(2),@week)+''' week #tempcontract'+@wherepriceweek if(@sortby='members') begin set @sqlstring = @sqlstring + ' union select unit, shortname, '''', ''zzzz'', '''', '''', ''0'' price, ''0'' variance, '''' priceweek, ''wk'+convert(varchar (2),@week)+''' week #tempcontract isnull(['+@lastpriceweek+'],''0.00'') <> ''999.99'' or isnull(['+@priceweek+'],''0.00'') <> ''999.99'' ' end else begin set @sqlstring = @sqlstring + ' union select unit, '''', itemcode, description, ''1000'', contractprice, ''0'' price, ''0'' variance, '''' priceweek, ''wk'+convert (varchar(2),@week)+''' week #tempcontract isnull(['+@lastpriceweek+'],''0.00'') <> ''999.99'' or isnull(['+@priceweek+'],''0.00'') <> ''999.99'' ' end end set @lastpriceweek = @priceweek fetch next setpriceweeksql @priceweek, @begdate end close setpriceweeksql deallocate setpriceweeksql print(@sqlstring) execute(@sqlstring) drop table #tempcontract if(@sortby='members') begin select unit, shortname, itemcode, description, regionorder, contractprice, convert(varchar(20),convert(decimal(10,2),price)) price, sum(convert(money,variance)) variance, varianceaverage = convert(varchar(25),convert(decimal(10,2),(select sum(convert (money,variance)) #temp shortname=t.shortname , week=t.week) / replace(((select count (regionorder) #temp shortname=t.shortname , week=t.week)-count(variance)),'0','1'))), priceweek,week #temp t group unit, shortname, itemcode, description, regionorder, contractprice, price, priceweek,week order week,shortname,description end else begin select unit, shortname, itemcode, description, regionorder, contractprice, convert(varchar(20),convert(decimal(10,2),price)) price, sum(convert(money,variance)) variance, varianceaverage = convert(varchar(25),convert(decimal(10,2),(select sum(convert (money,variance)) #temp itemcode=t.itemcode , week=t.week) / replace(((select count (regionorder) #temp itemcode=t.itemcode , week=t.week)-count(variance)),'0','1'))), priceweek,week #temp t group unit, shortname, itemcode, description, regionorder, contractprice, price, priceweek,week order week,description,regionorder end drop table #temp ...and 1 returns nothing:
if object_id ( 'sp_viewpricematrix_variance_rockbottomextended', 'p' ) not null drop procedure sp_viewpricematrix_variance_rockbottomextended; go create procedure [dbo].[sp_viewpricematrix_variance_rockbottomextended] @begdate datetime, @enddate datetime, @sortby varchar(20) declare @sqlstring varchar(max), @statement varchar(8000), @shortname varchar(50), @itemcode varchar(25), @priceweek varchar(30), @lastpriceweek varchar(30), @week int, @wherepriceweek varchar(2000), @price varchar(25), @contractprice int, @calendarbegdate datetime, @calendarenddate datetime create table #temp ( unit varchar(50), shortname varchar(25), itemcode varchar(50), description varchar(250), regionorder int, contractprice varchar(50), price varchar(25), variance varchar(25), priceweek varchar(50), week varchar(10) ) -- create temp table select up.unit, mm.shortname, up.itemcode, description=(select description masterproducts itemcode=up.itemcode), mm.regionorder, up.contractprice #tempcontract unitproducts inner join unitmembers um on up.unit=um.unit , abs(um.pricesheet) = 1 inner join members mm on um.memberno = mm.memberno upper(up.unit) in ('chophouse', 'craftworks sc', 'gordon biersch', 'old chi franchise', 'old chicago', 'rock bottom') select @calendarbegdate = c.begindate calendar c @begdate between c.begindate , c.enddate select @calendarenddate = c.enddate calendar c @enddate between c.begindate , c.enddate -- weeks , clause set @wherepriceweek = ' ' declare getpriceweek cursor select [priceweek] = c.description, c.begindate calendar c c.begindate <= @calendarenddate , c.enddate >= @calendarbegdate order 2 open getpriceweek fetch next getpriceweek @priceweek, @begdate while @@fetch_status = 0 begin select @statement = ('alter table #tempcontract add [' + @priceweek + '] numeric(8,2) ') exec (@statement) if(@wherepriceweek<>' ') begin set @wherepriceweek = @wherepriceweek + 'or ' end set @wherepriceweek = @wherepriceweek + 'isnull(['+@priceweek+'],''0.00'') <> ''999.99'' ' fetch next getpriceweek @priceweek, @begdate end close getpriceweek deallocate getpriceweek -- build member data weeks declare getmembercolumns cursor select distinct shortname,itemcode #tempcontract order shortname,itemcode open getmembercolumns fetch next getmembercolumns @shortname,@itemcode while @@fetch_status = 0 begin declare getmemberprice cursor select [priceweek] = c.description, convert(varchar(20), cast(isnull(mp.price,0) numeric(8,2))) price, up.contractprice memberprice mp inner join unitproducts on mp.unit=up.unit , mp.itemcode=up.itemcode inner join unitmembers um on mp.memberno=um.memberno , mp.unit=um.unit , abs (um.pricesheet) = 1 inner join members mm on mp.memberno = mm.memberno inner join calendar c on mp.cyear=c.cyear , mp.cweek=c.cweek upper(mp.unit) in ('chophouse', 'craftworks sc', 'gordon biersch', 'old chi franchise', 'old chicago', 'rock bottom') , c.begindate <= @calendarenddate , c.enddate >= @calendarbegdate , mm.shortname = @shortname , mp.itemcode = @itemcode open getmemberprice fetch next getmemberprice @priceweek,@price,@contractprice while @@fetch_status = 0 begin print(@price) select @statement = ('update #tempcontract set [' + @priceweek + ']=''' + isnull(@price,'0.00') + ''' itemcode=''' + @itemcode + ''' , unit in (''chophouse'', ''craftworks sc'', ''gordon biersch'', ''old chi franchise'', ''old chicago'', ''rock bottom'') , [shortname]=''' + @shortname +'''') exec (@statement) fetch next getmemberprice @priceweek,@price,@contractprice end close getmemberprice deallocate getmemberprice fetch next getmembercolumns @shortname,@itemcode end close getmembercolumns deallocate getmembercolumns --select * #tempcontract -- final select statement set @week = 0 set @lastpriceweek = '' set @sqlstring = '' declare setpriceweeksql cursor select [priceweek] = c.description, c.begindate calendar c c.begindate between @calendarbegdate , @calendarenddate order 2 open setpriceweeksql fetch next setpriceweeksql @priceweek, @begdate while @@fetch_status = 0 begin set @week = @week + 1 if(@sqlstring='') begin set @sqlstring = @sqlstring + 'insert #temp select unit, shortname, itemcode, description, regionorder, contractprice, isnull('+ '['+@priceweek+'],''0.00'') price, (convert(decimal(10,3),''-0.001'')) variance, '''+@priceweek+''' priceweek, ''wk'+convert(varchar(2),@week)+''' week #tempcontract'+@wherepriceweek if(@sortby='members') begin set @sqlstring = @sqlstring + ' union select unit, shortname, '''', ''zzzz'', '''', '''', ''0'' price, ''-0.001'' variance, '''' priceweek, ''wk'+convert(varchar (2),@week)+''' week #tempcontract'+@wherepriceweek end else begin set @sqlstring = @sqlstring + ' union select unit, '''', itemcode, description, ''1000'', contractprice, ''0'' price, ''-0.001'' variance, '''' priceweek, ''wk'+convert(varchar(2),@week)+''' week #tempcontract'+@wherepriceweek end end else begin set @sqlstring = @sqlstring + ' union ' set @sqlstring = @sqlstring + 'select unit, shortname, itemcode, description, regionorder, contractprice, isnull('+ '['+@priceweek+'],''0.00'') price, isnull(convert(decimal(10,2),['+@priceweek +'])-convert(decimal(10,2),['+@lastpriceweek+']),''0.00'') variance, '''+@priceweek+''' priceweek, ''wk'+convert(varchar(2),@week)+''' week #tempcontract'+@wherepriceweek if(@sortby='members') begin set @sqlstring = @sqlstring + ' union select unit, shortname, '''', ''zzzz'', '''', '''', ''0'' price, ''0'' variance, '''' priceweek, ''wk'+convert(varchar (2),@week)+''' week #tempcontract isnull(['+@lastpriceweek+'],''0.00'') <> ''999.99'' or isnull(['+@priceweek+'],''0.00'') <> ''999.99'' ' end else begin set @sqlstring = @sqlstring + ' union select unit, '''', itemcode, description, ''1000'', contractprice, ''0'' price, ''0'' variance, '''' priceweek, ''wk'+convert (varchar(2),@week)+''' week #tempcontract isnull(['+@lastpriceweek+'],''0.00'') <> ''999.99'' or isnull(['+@priceweek+'],''0.00'') <> ''999.99'' ' end end set @lastpriceweek = @priceweek fetch next setpriceweeksql @priceweek, @begdate end close setpriceweeksql deallocate setpriceweeksql print(@sqlstring) execute(@sqlstring) drop table #tempcontract if(@sortby='members') begin select unit, shortname, itemcode, description, regionorder, contractprice, convert(varchar(20),convert(decimal(10,2),price)) price, sum(convert(money,variance)) variance, varianceaverage = convert(varchar(25),convert(decimal(10,2),(select sum(convert (money,variance)) #temp shortname=t.shortname , week=t.week) / replace(((select count (regionorder) #temp shortname=t.shortname , week=t.week)-count(variance)),'0','1'))), priceweek,week #temp t group unit, shortname, itemcode, description, regionorder, contractprice, price, priceweek,week order week,shortname,description end else begin select unit, shortname, itemcode, description, regionorder, contractprice, convert(varchar(20),convert(decimal(10,2),price)) price, sum(convert(money,variance)) variance, varianceaverage = convert(varchar(25),convert(decimal(10,2),(select sum(convert (money,variance)) #temp itemcode=t.itemcode , week=t.week) / replace(((select count (regionorder) #temp itemcode=t.itemcode , week=t.week)-count(variance)),'0','1'))), priceweek,week #temp t group unit, shortname, itemcode, description, regionorder, contractprice, price, priceweek,week order week,description,regionorder end drop table #temp
there difference in 2 queries...
first query:
upper(up.unit) = 'rock bottom' , c.begindate <= @calendarenddate , c.enddate >= second query:
upper(mp.unit) in ('chophouse', 'craftworks sc', 'gordon biersch', 'old chi franchise', 'old chicago', you're referencing different table. make difference? notice in first (working) query reference up , second mp.
