SQL 2008 R2 - Syntax Error

  • 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

  • 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