Link to home
Start Free TrialLog in
Avatar of iscivanomar
iscivanomarFlag for United States of America

asked on

How can I use exist in store procedure?

This is part of the store procedure which is giving me an error because I am using more than one SELECT statements after EXISTS. How can I fix this error.  


DECLARE @ActualStorageLocation BIGINT
      
      SELECT top(1) @ActualStorageLocation = SL.Id
      FROM [WMS.Storage].[StorageLocation] SL
            JOIN
            [WMS.Storage].StoragePosition SP
            ON SL.Id = SP.Id_StorageLocation
      WHERE SL.Name = @FromLocation
      
      IF (@ActualStorageLocation IS NULL)
      BEGIN
            --SEND ERROR CODE " " LOCATION or NAME not found in [WMS.Storage.StorageLocation]
            SET @ResultErrorCode = 'CA'            
      END
      ELSE
      BEGIN
            --Check if the Ticket Number exist in E80 Inventory
            DECLARE @ActualStackUnit NVARCHAR(255) = NULL
            
            SELECT top(1) @ActualStackUnit = S.ID
            FROM WMS_Kraft.StockUnit SU
                  JOIN
                  [WMS.Inventory].StockUnit S
                  ON SU.ID = S.Id
            WHERE SU.TicketNumber = @TicketNumber   --OR  S.LPN = @TicketNumber
            AND S.Deleted = 0
            AND S.Destroyed = 0
            AND EXISTS (SELECT * FROM [WMS.Storage].StockUnitPosition SUP
                              WHERE SU.ID = SUP.Id_StockUnit
                              AND SUP.Id_StoragePosition
                              IN (SELECT * FROM [WMS.Storage].StoragePosition SP
                                    JOIN
                                     [WMS.Storage].StorageLocation SL
                                    ON SL.Id = SP.Id_StorageLocation
                              WHERE Id_StorageLocation = SL.Id))
            IF (@ActualStackUnit IS NULL)
            BEGIN
                  --SEND ERROR CODE " " TICKET NUMBER or LPN not found in [WMS.Inventory.StockUnit]
                  SET @ResultErrorCode = 'Not_LPN'
            END
            ELSE
            BEGIN
                  --DELETE THE INFORMATION FROM TABLES: [WMS.Inventory.StockUnit] & [WMS_Kraft.StockUnit]
                  --Where the ticket number is the same and also the ID
                        
                  SELECT @counter = @@ROWCOUNT, @error = @@ERROR
                  SET @ResultCode =      @counter;
                  SET @ResultMsg      =      'Location has being Cleared';
            END
      END
      
      
      
Avatar of radcaesar
radcaesar
Flag of India image

exists (select col1 from table1);
ASKER CERTIFIED SOLUTION
Avatar of Ephraim Wangoya
Ephraim Wangoya
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of iscivanomar

ASKER

Thank you for your help.
Have a good day.