Do While loop based on conditions in SQL Server [duplicate]

Question

How to implement a while loop in SQL server based on the below condition.

Then I need to execute select statement which returns ITEM_CODE ,It may have multiple rows too.

What I want to do inside the while loop is for each ITEM_CODE I need to get data from other tables (including joins ) and insert those data to a table .This loop will get end the count based on the first statements return.

Sample query structure:

SELECT ITEM_CODE  //While loop must execute the count of this rows
FROM   'TABLE_1'
WHERE  ITEM_AVAILABILITY = 'TRUE'

This statement will return a single row or may return multiple rows . I need to pass this ITEM_CODE to the while loop each time .Inside the while loop I will get the values from multiple tables and insert it to another table.

WHILE (@COUNT <>0){
//Need to have the ITEM_CODE while looping each time.
//Get data from multiple tables and assign to variables (SET @VARIABLES)
//Insert statement
IF (@COUNT = @COUNT)
     BREAK;
}

Is it possible with SQL server ,If Yes,please help me to fix this .

You had just asked basically the same question (and gotten responses!) only an hour ago - please do NOT repeat the same question over and over and over again — marc_s
– marc_s, Commented Dec 14, 2018 at 6:52
The main point is: you need to forget about looping and about RBAR (row-by-agonizing-row) processing as you might know from VB, PHP, C# or whatever other language you use. SQL is different and you need to adapt your mindset to use the proper set-based approach - do not loop - let SQL handle that for you. Just express (in SQL) what you want - don't tell SQL in detail how to do it (it can figure that out by itself, usually better than you can tell it) — marc_s
– marc_s, Commented Dec 14, 2018 at 7:00

gotqn · Accepted Answer · 2018-12-14 06:43:18Z

Try this:

DECLARE @DataSource TABLE
(
    [ITEM_CODE] VARCHAR(12)
);

INSER INTO @DataSource ([ITEM_CODE])
SELECT ITEM_CODE  //While loop must execute the count of this rows
FROM   'TABLE_1'
WHERE  ITEM_AVAILABILITY = 'TRUE';

DECLARe @CurrentItemCode VARCHAR(12);

WHILE(EXISTS (SELECT 1 FROM @DataSource))
BEGIN;

    SELECT  TOP 1 @CurrentItemCod = [ITEM_CODE] 
    FROM @DataSource

    --



    --

    DELETE FROM @DataSource
    WHERE [ITEM_CODE] = @CurrentItemCod;

END;

The idea is to perform a loop while there is data in our buffer table. Then in each iteration of the loop, get one random item code value, perform your internal operations and delete the value from the buffer.

Some people are using row ID column in the buffer table, for example INT IDENTITY(1,1) and are selecting the first element, doing the internal operations and then deleting by id. But you need to know the count of the records and to increment the id with each iteration - something like for cycle.

Collectives™ on Stack Overflow

Do While loop based on conditions in SQL Server [duplicate]

1 Answer 1

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Linked

Related