I have two Stored Procedures which are identical except for two things:
(a) One works (returns records) and the other doesn't (returns no records)
(b) The working one searches for a single match, whereas the nonworking one casts a wider net, including additional values in those being sought
The only differences (besides the SP names) between the two Stored Procedures are:
1) Where the 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 the working Stored Proc contains this:
where UPPER(up.Unit) = 'ROCK BOTTOM' and C.BeginDate <= @CalendarEndDate and 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') and C.BeginDate <= @CalendarEndDate and C.EndDate >= @CalendarBegDate
3)
And finally, where the working Stored Proc contains this:
and Unit = ''ROCK BOTTOM'' and
...the nonworking Stored Proc contains this:
and Unit IN (''CHOPHOUSE'', ''CRAFTWORKS SC'', ''GORDON BIERSCH'', ''OLD CHI FRANCHISE'', ''OLD CHICAGO'', ''ROCK BOTTOM'') and
Why would searching for 'Rock Bottom' along with other values fail, while looking for that value alone succeed?
At commenter Kritner's request, here are the Stored Procs in their entirety; the first one is the one that works:
IF OBJECT_ID ( 'sp_ViewPriceMatrix_Variance_RockBottomRollup_RockBottom', 'P' ) IS 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)
AS
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 from MasterProducts where ItemCode=up.itemcode),
mm.regionorder, up.Contractprice
into #TempContract From UnitProducts up
Inner Join Unitmembers um on up.Unit=um.Unit and abs(um.pricesheet) = 1
Inner Join Members mm on um.memberno = mm.memberno
where UPPER(up.Unit) = 'ROCK BOTTOM'
Select @CalendarBegDate = C.BeginDate From Calendar C where @BegDate between C.BeginDate and C.EndDate
Select @CalendarEndDate = C.EndDate From Calendar C where @EndDate between C.BeginDate and C.EndDate
-- get weeks and where clause
SET @WherePriceWeek = ' Where '
Declare GetPriceWeek Cursor For
Select [PriceWeek] = C.Description, C.BeginDate
From Calendar C
where C.BeginDate <= @CalendarEndDate and C.EndDate >= @CalendarBegDate
Order By 2
Open GetPriceWeek
fetch next from GetPriceWeek into @PriceWeek, @BegDate
while @@fetch_status = 0
begin
Select @Statement = ('Alter Table #TempContract Add [' + @PriceWeek + '] numeric(8,2) ')
exec (@Statement)
IF(@WherePriceWeek<>' Where ')
Begin
SET @WherePriceWeek = @WherePriceWeek + 'or '
End
SET @WherePriceWeek = @WherePriceWeek + 'IsNull(['+@PriceWeek+'],''0.00'') <> ''999.99'' '
fetch next from GetPriceWeek into @PriceWeek, @BegDate
end
Close GetPriceWeek
Deallocate GetPriceWeek
-- build member data by weeks
Declare GetMemberColumns Cursor For
SELECT distinct ShortName,ItemCode
FROM #TempContract
Order by ShortName,ItemCode
Open GetMemberColumns
fetch next from GetMemberColumns into @ShortName,@ItemCode
while @@fetch_status = 0
begin
Declare GetMemberPrice Cursor For
Select [PriceWeek] = C.Description, convert(varchar(20),
cast(IsNull(mp.Price,0) as numeric(8,2))) as Price,
up.Contractprice
From MemberPrice mp
Inner Join UnitProducts up on mp.unit=up.unit and mp.itemcode=up.itemcode
Inner Join Unitmembers um on mp.memberno=um.memberno and mp.unit=um.unit and abs
(um.pricesheet) = 1
Inner Join Members mm on mp.memberno = mm.memberno
Inner Join Calendar C on mp.CYear=C.CYear and mp.Cweek=C.CWeek
where UPPER(up.Unit) = 'ROCK BOTTOM' and C.BeginDate <= @CalendarEndDate and C.EndDate >=
@CalendarBegDate
and mm.ShortName = @ShortName and Mp.ItemCode = @ItemCode
Open GetMemberPrice
fetch next from GetMemberPrice into @PriceWeek,@Price,@Contractprice
while @@fetch_status = 0
begin
Print(@Price)
Select @Statement = ('Update #TempContract Set [' + @PriceWeek + ']=''' +
IsNull(@Price,'0.00') + ''' where ItemCode=''' + @ItemCode + ''' and Unit = ''ROCK BOTTOM'' and
[ShortName]=''' + @ShortName +'''')
exec (@Statement)
fetch next from GetMemberPrice into @PriceWeek,@Price,@Contractprice
end
Close GetMemberPrice
Deallocate GetMemberPrice
fetch next from GetMemberColumns into @ShortName,@ItemCode
end
Close GetMemberColumns
Deallocate GetMemberColumns
--Select * From #TempContract
-- final select statement
SET @Week = 0
SET @LastPriceWeek = ''
SET @SQLstring = ''
Declare SetPriceWeekSQL Cursor For
Select [PriceWeek] = C.Description, C.BeginDate
From Calendar C
where C.BeginDate between @CalendarBegDate and @CalendarEndDate
Order By 2
Open SetPriceWeekSQL
fetch next from SetPriceWeekSQL into @PriceWeek, @BegDate
while @@fetch_status = 0
begin
SET @Week = @Week + 1
IF(@SQLstring='')
Begin
SET @SQLstring = @SQLstring + 'Insert Into #Temp Select Unit, ShortName, ItemCode,
Description, regionorder, Contractprice, IsNull('+
'['+@PriceWeek+'],''0.00'') as Price, (convert(decimal(10,3),''-0.001'')) as
Variance,
'''+@PriceWeek+''' as PriceWeek, ''WK'+convert(varchar(2),@Week)+''' as Week From
#TempContract'+@WherePriceWeek
IF(@SortBy='Members')
Begin
SET @SQLstring = @SQLstring + ' UNION Select Unit, ShortName, '''',
''zzzz'', '''', '''', ''0'' as Price, ''-0.001'' as Variance, '''' as PriceWeek, ''WK'+convert(varchar
(2),@Week)+''' as Week From #TempContract'+@WherePriceWeek
End
Else
Begin
SET @SQLstring = @SQLstring + ' UNION Select Unit, '''', ItemCode,
Description, ''1000'', Contractprice, ''0'' as Price, ''-0.001'' as Variance, '''' as PriceWeek,
''WK'+convert(varchar(2),@Week)+''' as Week From #TempContract'+@WherePriceWeek
End
End
ELSE
Begin
SET @SQLstring = @SQLstring + ' UNION '
SET @SQLstring = @SQLstring + 'Select Unit, ShortName, ItemCode, Description,
regionorder, Contractprice, IsNull('+
'['+@PriceWeek+'],''0.00'') as Price, IsNull(convert(decimal(10,2),['+@PriceWeek
+'])-convert(decimal(10,2),['+@LastPriceWeek+']),''0.00'') as Variance,
'''+@PriceWeek+''' as PriceWeek, ''WK'+convert(varchar(2),@Week)+''' as Week From
#TempContract'+@WherePriceWeek
IF(@SortBy='Members')
Begin
SET @SQLstring = @SQLstring + ' UNION Select Unit, ShortName, '''',
''zzzz'', '''', '''', ''0'' as Price, ''0'' as Variance, '''' as PriceWeek, ''WK'+convert(varchar
(2),@Week)+''' as Week From #TempContract Where 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'' as Price, ''0'' as Variance, '''' as PriceWeek, ''WK'+convert
(varchar(2),@Week)+''' as Week From #TempContract Where IsNull(['+@LastPriceWeek+'],''0.00'') <> ''999.99''
or IsNull(['+@PriceWeek+'],''0.00'') <> ''999.99'' '
End
End
SET @LastPriceWeek = @PriceWeek
fetch next from SetPriceWeekSQL into @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)) as Price,
sum(convert(money,Variance)) as Variance,
VarianceAverage = convert(varchar(25),convert(decimal(10,2),(Select sum(convert
(money,Variance)) From #Temp Where ShortName=T.ShortName and Week=T.Week) / Replace(((Select count
(regionorder) From #Temp Where ShortName=T.ShortName and Week=T.Week)-count(Variance)),'0','1'))),
PriceWeek,Week
From #Temp T
Group By
Unit,
ShortName,
ItemCode,
Description,
regionorder,
Contractprice,
Price,
PriceWeek,Week
Order By Week,ShortName,Description
End
ELSE
Begin
Select
Unit,
ShortName,
ItemCode,
Description,
regionorder,
Contractprice,
convert(varchar(20),convert(decimal(10,2),Price)) as Price,
sum(convert(money,Variance)) as Variance,
VarianceAverage = convert(varchar(25),convert(decimal(10,2),(Select sum(convert
(money,Variance)) From #Temp Where ItemCode=T.ItemCode and Week=T.Week) / Replace(((Select count
(regionorder) From #Temp Where ItemCode=T.ItemCode and Week=T.Week)-count(Variance)),'0','1'))),
PriceWeek,Week
From #Temp T
Group By
Unit,
ShortName,
ItemCode,
Description,
regionorder,
Contractprice,
Price,
PriceWeek,Week
Order By Week,Description,regionorder
End
Drop Table #Temp
...and this is the one that returns nothing:
IF OBJECT_ID ( 'sp_ViewPriceMatrix_Variance_RockBottomExtended', 'P' ) IS NOT NULL
DROP PROCEDURE sp_ViewPriceMatrix_Variance_RockBottomExtended;
GO
CREATE Procedure [dbo].[sp_ViewPriceMatrix_Variance_RockBottomExtended]
@BegDate datetime,
@EndDate datetime,
@SortBy varchar(20)
AS
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 from MasterProducts where ItemCode=up.itemcode),
mm.regionorder, up.Contractprice
into #TempContract From UnitProducts up
Inner Join Unitmembers um on up.Unit=um.Unit and abs(um.pricesheet) = 1
Inner Join Members mm on um.memberno = mm.memberno
where UPPER(up.Unit) IN ('CHOPHOUSE', 'CRAFTWORKS SC', 'GORDON BIERSCH', 'OLD CHI FRANCHISE', 'OLD CHICAGO', 'ROCK BOTTOM')
Select @CalendarBegDate = C.BeginDate From Calendar C where @BegDate between C.BeginDate and C.EndDate
Select @CalendarEndDate = C.EndDate From Calendar C where @EndDate between C.BeginDate and C.EndDate
-- get weeks and where clause
SET @WherePriceWeek = ' Where '
Declare GetPriceWeek Cursor For
Select [PriceWeek] = C.Description, C.BeginDate
From Calendar C
where C.BeginDate <= @CalendarEndDate and C.EndDate >= @CalendarBegDate
Order By 2
Open GetPriceWeek
fetch next from GetPriceWeek into @PriceWeek, @BegDate
while @@fetch_status = 0
begin
Select @Statement = ('Alter Table #TempContract Add [' + @PriceWeek + '] numeric(8,2) ')
exec (@Statement)
IF(@WherePriceWeek<>' Where ')
Begin
SET @WherePriceWeek = @WherePriceWeek + 'or '
End
SET @WherePriceWeek = @WherePriceWeek + 'IsNull(['+@PriceWeek+'],''0.00'') <> ''999.99'' '
fetch next from GetPriceWeek into @PriceWeek, @BegDate
end
Close GetPriceWeek
Deallocate GetPriceWeek
-- build member data by weeks
Declare GetMemberColumns Cursor For
SELECT distinct ShortName,ItemCode
FROM #TempContract
Order by ShortName,ItemCode
Open GetMemberColumns
fetch next from GetMemberColumns into @ShortName,@ItemCode
while @@fetch_status = 0
begin
Declare GetMemberPrice Cursor For
Select [PriceWeek] = C.Description, convert(varchar(20),
cast(IsNull(mp.Price,0) as numeric(8,2))) as Price,
up.Contractprice
From MemberPrice mp
Inner Join UnitProducts up on mp.unit=up.unit and mp.itemcode=up.itemcode
Inner Join Unitmembers um on mp.memberno=um.memberno and mp.unit=um.unit and abs
(um.pricesheet) = 1
Inner Join Members mm on mp.memberno = mm.memberno
Inner Join Calendar C on mp.CYear=C.CYear and mp.Cweek=C.CWeek
where UPPER(mp.Unit) IN ('CHOPHOUSE', 'CRAFTWORKS SC', 'GORDON BIERSCH', 'OLD CHI FRANCHISE', 'OLD CHICAGO', 'ROCK BOTTOM') and C.BeginDate <= @CalendarEndDate and C.EndDate >= @CalendarBegDate
and mm.ShortName = @ShortName and Mp.ItemCode = @ItemCode
Open GetMemberPrice
fetch next from GetMemberPrice into @PriceWeek,@Price,@Contractprice
while @@fetch_status = 0
begin
Print(@Price)
Select @Statement = ('Update #TempContract Set [' + @PriceWeek + ']=''' +
IsNull(@Price,'0.00') + ''' where ItemCode=''' + @ItemCode + ''' and Unit IN (''CHOPHOUSE'', ''CRAFTWORKS SC'', ''GORDON BIERSCH'', ''OLD CHI FRANCHISE'', ''OLD CHICAGO'', ''ROCK BOTTOM'') and
[ShortName]=''' + @ShortName +'''')
exec (@Statement)
fetch next from GetMemberPrice into @PriceWeek,@Price,@Contractprice
end
Close GetMemberPrice
Deallocate GetMemberPrice
fetch next from GetMemberColumns into @ShortName,@ItemCode
end
Close GetMemberColumns
Deallocate GetMemberColumns
--Select * From #TempContract
-- final select statement
SET @Week = 0
SET @LastPriceWeek = ''
SET @SQLstring = ''
Declare SetPriceWeekSQL Cursor For
Select [PriceWeek] = C.Description, C.BeginDate
From Calendar C
where C.BeginDate between @CalendarBegDate and @CalendarEndDate
Order By 2
Open SetPriceWeekSQL
fetch next from SetPriceWeekSQL into @PriceWeek, @BegDate
while @@fetch_status = 0
begin
SET @Week = @Week + 1
IF(@SQLstring='')
Begin
SET @SQLstring = @SQLstring + 'Insert Into #Temp Select Unit, ShortName, ItemCode,
Description, regionorder, Contractprice, IsNull('+
'['+@PriceWeek+'],''0.00'') as Price, (convert(decimal(10,3),''-0.001'')) as
Variance,
'''+@PriceWeek+''' as PriceWeek, ''WK'+convert(varchar(2),@Week)+''' as Week From
#TempContract'+@WherePriceWeek
IF(@SortBy='Members')
Begin
SET @SQLstring = @SQLstring + ' UNION Select Unit, ShortName, '''',
''zzzz'', '''', '''', ''0'' as Price, ''-0.001'' as Variance, '''' as PriceWeek, ''WK'+convert(varchar
(2),@Week)+''' as Week From #TempContract'+@WherePriceWeek
End
Else
Begin
SET @SQLstring = @SQLstring + ' UNION Select Unit, '''', ItemCode,
Description, ''1000'', Contractprice, ''0'' as Price, ''-0.001'' as Variance, '''' as PriceWeek,
''WK'+convert(varchar(2),@Week)+''' as Week From #TempContract'+@WherePriceWeek
End
End
ELSE
Begin
SET @SQLstring = @SQLstring + ' UNION '
SET @SQLstring = @SQLstring + 'Select Unit, ShortName, ItemCode, Description,
regionorder, Contractprice, IsNull('+
'['+@PriceWeek+'],''0.00'') as Price, IsNull(convert(decimal(10,2),['+@PriceWeek
+'])-convert(decimal(10,2),['+@LastPriceWeek+']),''0.00'') as Variance,
'''+@PriceWeek+''' as PriceWeek, ''WK'+convert(varchar(2),@Week)+''' as Week From
#TempContract'+@WherePriceWeek
IF(@SortBy='Members')
Begin
SET @SQLstring = @SQLstring + ' UNION Select Unit, ShortName, '''',
''zzzz'', '''', '''', ''0'' as Price, ''0'' as Variance, '''' as PriceWeek, ''WK'+convert(varchar
(2),@Week)+''' as Week From #TempContract Where 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'' as Price, ''0'' as Variance, '''' as PriceWeek, ''WK'+convert
(varchar(2),@Week)+''' as Week From #TempContract Where IsNull(['+@LastPriceWeek+'],''0.00'') <> ''999.99''
or IsNull(['+@PriceWeek+'],''0.00'') <> ''999.99'' '
End
End
SET @LastPriceWeek = @PriceWeek
fetch next from SetPriceWeekSQL into @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)) as Price,
sum(convert(money,Variance)) as Variance,
VarianceAverage = convert(varchar(25),convert(decimal(10,2),(Select sum(convert
(money,Variance)) From #Temp Where ShortName=T.ShortName and Week=T.Week) / Replace(((Select count
(regionorder) From #Temp Where ShortName=T.ShortName and Week=T.Week)-count(Variance)),'0','1'))),
PriceWeek,Week
From #Temp T
Group By
Unit,
ShortName,
ItemCode,
Description,
regionorder,
Contractprice,
Price,
PriceWeek,Week
Order By Week,ShortName,Description
End
ELSE
Begin
Select
Unit,
ShortName,
ItemCode,
Description,
regionorder,
Contractprice,
convert(varchar(20),convert(decimal(10,2),Price)) as Price,
sum(convert(money,Variance)) as Variance,
VarianceAverage = convert(varchar(25),convert(decimal(10,2),(Select sum(convert
(money,Variance)) From #Temp Where ItemCode=T.ItemCode and Week=T.Week) / Replace(((Select count
(regionorder) From #Temp Where ItemCode=T.ItemCode and Week=T.Week)-count(Variance)),'0','1'))),
PriceWeek,Week
From #Temp T
Group By
Unit,
ShortName,
ItemCode,
Description,
regionorder,
Contractprice,
Price,
PriceWeek,Week
Order By Week,Description,regionorder
End
Drop Table #Temp
There is another difference in the two queries...
First query:
where UPPER(up.Unit) = 'ROCK BOTTOM' and C.BeginDate <= @CalendarEndDate and C.EndDate >=
Second query:
where UPPER(mp.Unit) IN ('CHOPHOUSE', 'CRAFTWORKS SC', 'GORDON BIERSCH', 'OLD CHI FRANCHISE', 'OLD CHICAGO',
You're referencing a different table. Does that make a difference? Notice in the first (working) query you reference up and the second is mp.