Invalid column name - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Invalid column name

Author

Topic

vbjohn
Starting Member

32 Posts

Posted - 2012-02-01 : 11:27:39

I am getting an: Invalid column name 'StoreNumber' error

Can anyone see what the issue is?

create procedure dbo.findappartsbyinventorynumber
(
@YardNumber int,
@StoreNumber int = null,
@InventoryNumber inventorynumber,
@CategoryCode char(1) = Null
)

as
set nocount on

declare
@CategoryInclusions varchar(50),
@AIMWholeSalePrice BIT,
@positionOfWildCard int

-- get system defaults
select
@AIMWholeSalePrice = AIMWholeSalePrice,
@CategoryInclusions = CategoryInclusions
from AIM with (NOLOCK)
Where YardNumber = @YardNumber

select @positionOfWildCard = CHARINDEX('%', @InventoryNumber)

if(@positionOfWildCard > 0) begin
select inv.InventoryID,
inv.CategorizingYardNumber as YardNumber,
inv.CategorizingStoreNumber as StoreNumber,
inv.CategoryCode,
inv.InventoryNumber,
inv.PartRating,
inv.RetailPrice,
inv.DateRetailLastChanged,
inv.DirectionOfRetailPriceChange,
inv.WholesalePrice,
inv.DateWholesaleLastChanged,
inv.DirectionOfWholesalePriceChange,
inv.ConditionsAndOptions,
inv.ModelName,
inv.ModelYear,
inv.Mileage,
inv_cat.StandardRetailPrice,
inv_cat.RetailPriceLastChangeDate,
inv_cat.StandardWholesalePrice,
inv_cat.WholesalePriceLastChangeDate,
inv_cat.AvgSellPrice,
inv_cat.StandardOldPrice

FROM INVENTORY inv with (NOLOCK)
INNER JOIN INVENTORY_CATEGORY inv_cat with (NOLOCK)
ON (inv.CategorizingYardNumber = inv_cat.YardNumber)
AND (inv.CategorizingStoreNumber = inv_cat.StoreNumber)
AND (inv.CategoryCode = inv_cat.CategoryCode)
AND (inv.InventoryNumber = inv_cat.InventoryNumber)

where inv.CategorizingYardNumber = @YardNumber
and inv.CategorizingStoreNumber = isnull(@StoreNumber, inv.CategorizingStoreNumber)
and inv.InventoryNumber like @InventoryNumber
and inv.CategoryCode = isnull(@CategoryCode, inv.CategoryCode)
and inv.CategoryCode in (select value from fn_Split(@CategoryInclusions,','))
and inv.PartRating In ('A','B')
and case WHEN @AIMWholeSalePrice=0 THEN isnull(RetailPriceLastChangeDate,0) ELSE isnull(WholesalePriceLastChangeDate,0) END > case WHEN @AIMWholeSalePrice=0 THEN isnull(DateRetailLastChanged,0) ELSE isnull(DateWholesaleLastChanged,0) END
and inv_cat.StandardOldPrice is not Null
and ((inv_cat.StandardOldPrice <> case WHEN @AIMWholeSalePrice=0 THEN inv_cat.StandardRetailPrice ELSE inv_cat.StandardWholesalePrice END) or
(case WHEN @AIMWholeSalePrice=0 THEN RetailPrice ELSE WholesalePrice END = 0))
and ((inv.QuantityAvailable > 0) or ((inv.QuantityAvailable = 0) and (inv.IsRowReusable = 1)))

order by Inv.InventoryNumber, Inv.StoreNumber
end
else
begin
select inv.InventoryID,
inv.CategorizingYardNumber as YardNumber,
inv.CategorizingStoreNumber as StoreNumber,
inv.CategoryCode,
inv.InventoryNumber,
inv.PartRating,
inv.RetailPrice,
inv.DateRetailLastChanged,
inv.DirectionOfRetailPriceChange,
inv.WholesalePrice,
inv.DateWholesaleLastChanged,
inv.DirectionOfWholesalePriceChange,
inv.ConditionsAndOptions,
inv.ModelName,
inv.ModelYear,
inv.Mileage,
inv_cat.StandardRetailPrice,
inv_cat.RetailPriceLastChangeDate,
inv_cat.StandardWholesalePrice,
inv_cat.WholesalePriceLastChangeDate,
inv_cat.AvgSellPrice,
inv_cat.StandardOldPrice

FROM INVENTORY inv with (NOLOCK)
INNER JOIN INVENTORY_CATEGORY inv_cat with (NOLOCK)
ON (inv.CategorizingYardNumber = inv_cat.YardNumber)
AND (inv.CategorizingStoreNumber = inv_cat.StoreNumber)
AND (inv.CategoryCode = inv_cat.CategoryCode)
AND (inv.InventoryNumber = inv_cat.InventoryNumber)

where inv.CategorizingYardNumber = @YardNumber
and inv.CategorizingStoreNumber = isnull(@StoreNumber, inv.CategorizingStoreNumber)
and inv.InventoryNumber = @InventoryNumber
and inv.CategoryCode = isnull(@CategoryCode, inv.CategoryCode)
and (inv.CategoryCode in (select value from fn_Split(@CategoryInclusions,',')))
and inv.PartRating In ('A','B')
and case WHEN @AIMWholeSalePrice=0 THEN isnull(RetailPriceLastChangeDate,0) ELSE isnull(WholesalePriceLastChangeDate,0) END > case WHEN @AIMWholeSalePrice=0 THEN isnull(DateRetailLastChanged,0) ELSE isnull(DateWholesaleLastChanged,0) END
and isnull(inv_cat.StandardOldPrice,0) > 0
and ((inv_cat.StandardOldPrice <> case WHEN @AIMWholeSalePrice=0 THEN inv_cat.StandardRetailPrice ELSE inv_cat.StandardWholesalePrice END) or
(case WHEN @AIMWholeSalePrice=0 THEN RetailPrice ELSE WholesalePrice END = 0))
and ((inv.QuantityAvailable > 0) or ((inv.QuantityAvailable = 0) and (inv.IsRowReusable = 1)))

order by Inv.InventoryNumber, Inv.StoreNumber
end

set nocount off

return (0)

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-01 : 12:13:29

do you've storenumber field in INVENTORY table?
try modifying


order by Inv.InventoryNumber, Inv.StoreNumber

to


order by Inv.InventoryNumber, StoreNumber

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

vbjohn
Starting Member

32 Posts

Posted - 2012-02-01 : 12:24:02

Thanks! It works great now.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-01 : 13:11:14

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources