November 9, 2010 at 3:10 pm
I encountered this strange behavior with SQL 2008 R2 Query Parser. The following proc parse successfully in VS 2008 DB, but failed in the Management Studio. The funny thing is the DB project which contains this proc was able to deploy from VS. But the CREATE script generated by SQL 2008 R2 management studio fails the validation. Any idea?
CREATE PROCEDURE [dbo].[WRHS_chkBinTransferItem]
(
@BinFromID int,
@BarCodeNumber varchar(50),
@Count int,
@Message varchar(500) OUTPUT
)
AS
DECLARE
@LotTrackIND int,
@ItemID int,
@QtyAvail int,
@LotNumber varchar(50)
SELECT @ItemID = i.ItemID, @LotTrackIND = i.LotTrackingInd, @LotNumber = ''
FROM WRHS_ItemUOM iu
INNER JOIN WRHS_Item i ON iu.ItemID = i.ItemID
WHERE iu.BarcodeNumber = @BarCodeNumber
UNION
SELECT @ItemID = i.ItemID, @LotTrackIND = i.LotTrackingInd, @LotNumber = wil.LotNumber
FROM WRHS_WarehouseItemLotIdentify wili
INNER JOIN WRHS_WarehouseItemLot wil ON wili.WarehouseItemLotID = wil.WarehouseItemLotID
INNER JOIN WRHS_WarehouseItem wi ON wil.WarehouseItemID = wi.WarehouseItemID
INNER JOIN WRHS_Item i ON wi.ItemID = i.ItemID
WHERE wili.ItemIdentification = @BarCodeNumber
IF @LotTrackIND = 1
BEGIN
IF @ItemID > 0
BEGIN
SELECT DISTINCT@QtyAvail = q.Qty
FROM WRHS_BinItem bi
INNER JOIN WRHS_BinItemQty q ON bi.BinItemID = q.BinItemID
WHERE bi.BinID = @BinFromID AND bi.ItemID = @ItemID AND q.LotNumber = @LotNumber
IF @Count <= @QtyAvail
BEGIN
SELECT DISTINCTq.BinItemQtyID, RTRIM(i.ItemNumber) as ItemNumber, i.LicensePlateInd, q.LotNumber, i.LotTrackingInd, i.ItemID, wi.ForcedPutAwayInd
FROM WRHS_Bin b
INNER JOIN WRHS_BinItem bi ON b.BinID = bi.BinID
INNER JOIN WRHS_BinItemQty q ON bi.BinItemID = q.BinItemID
INNER JOIN WRHS_Item i On bi.ItemID = i.ItemID
INNER JOIN WRHS_WarehouseItem wi ON b.WarehouseID = wi.WarehouseID AND i.ItemID = wi.ItemID
WHERE b.BinID = @BinFromID AND bi.ItemID = @ItemID AND q.LotNumber = @LotNumber
SET @Message = 'Success'
END
ELSE
BEGIN
SET @Message = 'Selected Quantity not Available'
END
END
ELSE
BEGIN
SET @Message = 'Invalid BarCode Scan, this Item is Lot Tracked and the proper BarCode must be scanned.'
END
END
ELSE
BEGIN
IF @ItemID > 0
BEGIN
SELECT DISTINCT@QtyAvail = q.Qty
FROM WRHS_BinItem bi
INNER JOIN WRHS_BinItemQty q ON bi.BinItemID = q.BinItemID
WHERE bi.BinID = @BinFromID AND bi.ItemID = @ItemID AND q.LotNumber = @LotNumber
IF @Count <= @QtyAvail
BEGIN
SELECT DISTINCTq.BinItemQtyID, RTRIM(i.ItemNumber) as ItemNumber, i.LicensePlateInd, q.LotNumber, i.LotTrackingInd, i.ItemID, wi.ForcedPutAwayInd
FROM WRHS_Bin b
INNER JOIN WRHS_BinItem bi ON b.BinID = bi.BinID
INNER JOIN WRHS_BinItemQty q ON bi.BinItemID = q.BinItemID
INNER JOIN WRHS_ItemUOM iu ON bi.ItemID = iu.ItemID
INNER JOIN WRHS_Item i On bi.ItemID = i.ItemID
INNER JOIN WRHS_WarehouseItem wi ON b.WarehouseID = wi.WarehouseID AND i.ItemID = wi.ItemID
WHERE b.BinID = @BinFromID AND bi.ItemID = @ItemID AND q.LotNumber = @LotNumber
SET @Message = 'Success'
END
ELSE
BEGIN
SET @Message = 'Selected Quantity not Available'
END
END
ELSE
BEGIN
SET @Message = 'Invalid BarCode Scan'
END
END
November 10, 2010 at 3:39 am
Did the error message give you a clue?
Would be nice to see it.
What's this bit trying to do?
SELECT @ItemID = i.ItemID, @LotTrackIND = i.LotTrackingInd, @LotNumber = ''
FROM WRHS_ItemUOM iu
INNER JOIN WRHS_Item i ON iu.ItemID = i.ItemID
WHERE iu.BarcodeNumber = @BarCodeNumber
UNION
SELECT @ItemID = i.ItemID, @LotTrackIND = i.LotTrackingInd, @LotNumber = wil.LotNumber
FROM WRHS_WarehouseItemLotIdentify wili
INNER JOIN WRHS_WarehouseItemLot wil ON wili.WarehouseItemLotID = wil.WarehouseItemLotID
INNER JOIN WRHS_WarehouseItem wi ON wil.WarehouseItemID = wi.WarehouseItemID
INNER JOIN WRHS_Item i ON wi.ItemID = i.ItemID
WHERE wili.ItemIdentification = @BarCodeNumber
Setting variables using a union?
Cursors never.
DTS - only when needed and never to control.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply