I am trying to use while loop instead of CURSOR in SQL SERVER. I am trying to select TOP 1 in while and set them to the variables like below. It doesnt let me set the variables in while loop. What am I doing wrong?
WHILE (
SELECT TOP 1 @WAOR_CODE = WAOR_.WAOR_CODE
, @WAOD_INVENTORYITEMID = WAOD_.WAOD_INVENTORYITEMID
FROM #wmsorder
)
BEGIN
SELECT @WAOR_CODE
, @WAOD_INVENTORYITEMID
DELETE TOP (1) #wmsorder
END
Another option:
WHILE EXISTS(select 1 FROM #wmsorder)
BEGIN
DELETE TOP (1)
FROM #wmsorder
END
However, deleting all records from a table one by one might be a performance hell. You might want to consider using TRUNCATE TABLE instead:
TRUNCATE TABLE #wmsorder
Also, note that each delete is written to the database log, while truncate table doesn't get written to the log at all.
Testing with a temporary table containing 100,000 rows, deleting the rows one by one took me 9 seconds, while truncate table completed immediately:
-- create and populate sample table
SELECT TOP 100000 IDENTITY(int,1,1) AS Number
INTO #wmsorder
FROM sys.objects s1
CROSS JOIN sys.objects s2
-- delete rows one by one
WHILE EXISTS(select 1 FROM #wmsorder)
BEGIN
DELETE TOP (1)
FROM #wmsorder
END
-- clean up
DROP TABLE #wmsorder
-- create and populate sample table
SELECT TOP 100000 IDENTITY(int,1,1) AS Number
INTO #wmsorder
FROM sys.objects s1
CROSS JOIN sys.objects s2
-- truncate the table
TRUNCATE TABLE #wmsorder
-- clean up
DROP TABLE #wmsorder
DECLARE @t TABLE (a INT PRIMARY KEY)
INSERT INTO @t
VALUES (1), (2), (3)
Variant #1:
label:
DELETE TOP(1)
FROM @t
OUTPUT DELETED.a
IF @@ROWCOUNT > 0
GOTO label
Variant #2:
WHILE @@ROWCOUNT != 0
DELETE TOP(1)
FROM @t
OUTPUT DELETED.a
Variant #3:
DECLARE @a TABLE(a INT)
WHILE @@ROWCOUNT != 0 BEGIN
DELETE FROM @a
DELETE TOP(1)
FROM @t
OUTPUT DELETED.a INTO @a
SELECT * FROM @a
END
See the below code. I just corrected the SQL statements shared by you
WHILE 1=1
BEGIN
IF NOT EXISTS (SELECT 1 FROM #wmsorder)
BREAK
SELECT TOP 1 @WAOR_CODE = WAOR_.WAOR_CODE
,@WAOD_INVENTORYITEMID = WAOD_.WAOD_INVENTORYITEMID
FROM #wmsorder WAOR_
SELECT @WAOR_CODE
,@WAOD_INVENTORYITEMID
DELETE #wmsorder WHERE WAOR_CODE = @WAOR_CODE AND WAOD_INVENTORYITEMID = @WAOD_INVENTORYITEMID
END
But as Zohar Peled mentioned, it will be a pain to the engine if you are deleting the records one by one from a table. So below I have shared another query, through this even you can track the records before deleting from the table
DECLARE @TableVar AS TABLE (WAOR_CODE VARCHAR(100), WAOD_INVENTORYITEMID VARCHAR(100))
WHILE 1=1
BEGIN
SELECT TOP 1 @WAOR_CODE = WAOR_.WAOR_CODE
,@WAOD_INVENTORYITEMID = WAOD_.WAOD_INVENTORYITEMID
FROM #wmsorder WAOR_
WHERE NOT EXISTS (SELECT 1 FROM @TableVar t WHERE t.WAOR_CODE = WAOR_.WAOR_CODE AND t.WAOD_INVENTORYITEMID = WAOR_.WAOD_INVENTORYITEMID)
IF @WAOR_CODE IS NULL AND @WAOD_INVENTORYITEMID IS NULL
BREAK
INSERT INTO @TableVar
(WAOR_CODE, WAOD_INVENTORYITEMID)
SELECT @WAOR_CODE
,@WAOD_INVENTORYITEMID
END
DELETE #wmsorder WHERE EXISTS (SELECT 1 FROM @TableVar t WHERE t.WAOR_CODE = #wmsorder.WAOR_CODE AND t.WAOD_INVENTORYITEMID = #wmsorder.WAOD_INVENTORYITEMID)
Sorry I did not test the second code. Please forgive me if it breaks something. But I am pretty sure it may require a small repair to make this query functional. All the best.
