iscivanomar
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].[StorageLoca tion] SL
JOIN
[WMS.Storage].StoragePosit ion 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.StorageLocati on]
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].StockUnitPos ition SUP
WHERE SU.ID = SUP.Id_StockUnit
AND SUP.Id_StoragePosition
IN (SELECT * FROM [WMS.Storage].StoragePosit ion SP
JOIN
[WMS.Storage].StorageLocat ion 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
DECLARE @ActualStorageLocation BIGINT
SELECT top(1) @ActualStorageLocation = SL.Id
FROM [WMS.Storage].[StorageLoca
JOIN
[WMS.Storage].StoragePosit
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.StorageLocati
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].StockUnitPos
WHERE SU.ID = SUP.Id_StockUnit
AND SUP.Id_StoragePosition
IN (SELECT * FROM [WMS.Storage].StoragePosit
JOIN
[WMS.Storage].StorageLocat
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
exists (select col1 from table1);
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you for your help.
Have a good day.
Have a good day.