We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

How can I use exist in store procedure?

Medium Priority
264 Views
Last Modified: 2012-05-11
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
      
      
      
Comment
Watch Question

exists (select col1 from table1);
Software Engineer
CERTIFIED EXPERT
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
Alpesh PatelSolution Architect at TCS (SAFe®Agilist, TOGAF® Foundation, PSM I®, MCP, MCSD)
CERTIFIED EXPERT
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:
Thank you for your help.
Have a good day.
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.