Variable assignment is not allowed in a statement containing a top level UNION, INTERSECT or EXCEPT operator

Running SQL 2008 R2. This proc passed the validation in VS 2008 DB Project, but failed in management studio. 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 DISTINCT q.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 DISTINCT q.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

for the assignment, part, you can work out it with the help of subqueries, for example:

Select @ItemID = ItemID, @LotTrackIND = LotTrackingInd, @LotNumber = LotNumber
  from (
        SELECT i.ItemID, i.LotTrackingInd, '' LotNumber
        FROM WRHS_ItemUOM iu
        INNER JOIN WRHS_Item i ON iu.ItemID = i.ItemID
        WHERE iu.BarcodeNumber = @BarCodeNumber
        UNION
        SELECT i.ItemID, i.LotTrackingInd, 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
       ) q1

I wanrn you're not iterating over the (possible) multiple records your query may return.

Best regards.

in your union you can't do variable assignments

SELECT @ItemID = i.ItemID, @LotTrackIND = i.LotTrackingInd, @LotNumber = ''  
...
UNION
SELECT @ItemID = i.ItemID, @LotTrackIND = i.LotTrackingInd, @LotNumber = wil.LotNumber 
...  

as the error says.

As for why it passed in VS, it's a VS bug.

Yeah the error is in this part,

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 

You can't assign values to variables using a select statement , if the statement has a UNION. If you think about it , it doesnt make sense. The variable can hold only one value, but which one should it choose? the one from the first select statement or the second one?

You have to make that choice explicit. Assuming that you want the second select statement to take precedence (i.e take the values from the second select. if no values are returned by the second select statement, then take the values returned by the first), you can do something like this

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 

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