CODEWITHSUNDEEP
sqlsql-servert-sql
fUrious
fUrious

Reputation: 444

Can I loop a list of a select statement?

I want to loop thru a list of products, where the list is a SELECT statement. Right now it is just a INT variable that is incremented:

DECLARE @i INT
SELECT @i = 0

WHILE( @i < 100 )
BEGIN
    ...

    SET @i = @i + 1
END

This is not what I want. How can I modify this so that @i is a element of a statement, something along the lines of (pseudo-ish):

DECLARE @ids List<INT>
SELECT @ids = (SELECT p.Id FROM Products p WHERE p.Active = 1)

FOR(@id in @ids)
BEGIN
    ...
END

The BEGIN END section is a couple of several lines of statements pulling out this and that with products with id of @i from the first. But I thought I could create a list of active products instead of adding the AND p.Active = 1 to all the statements in the BEGIN END section. This is for a Microsoft SQL Server using Microsoft SQL Server Management Studio.

Upvotes: 0

Views: 1767

Answers (1)

Pawel Czapski
Pawel Czapski

Reputation: 1864

Try below 2 options, let me know if any error occur.

Option 1. Declare variable table with extra identity column, then loop this variable table if there is any data.

DECLARE @VarTable TABLE (ORDER_ID int IDENTITY(1,1), ID INT not null)
INSERT INTO @VarTable
SELECT p.Id 
FROM Products p 
WHERE p.Active = 1

DECLARE @order_id int = 1
DECLARE @max_order_id int = (SELECT ISNULL(MAX(ORDER_ID),0) FROM @VarTable)
WHILE (@order_id <= @max_order_id)
BEGIN
    -- do your stuff
    SET @order_id = @order_id + 1
END

Option 2. You can use cursor to loop through IDs from your select statement.

DECLARE @ID INT

DECLARE CRS_CURSOR CURSOR FOR 
SELECT P.Id 
FROM Products P
WHERE P.Active = 1

OPEN CRS_CURSOR

FETCH NEXT FROM CRS_CURSOR INTO @ID

WHILE @@FETCH_STATUS = 0
BEGIN
    -- below line to test only
    SELECT @ID AS CurrentID
    -- do your stuff based on current @ID

    FETCH NEXT FROM CRS_CURSOR INTO @ID
END

CLOSE CRS_CURSOR
DEALLOCATE CRS_CURSOR

Upvotes: 1

Related Questions