How to set a variable in an sql stored procedure inside a nested loop

Question

In the 2nd loop of the given stored sql procedure

BEGIN
    SET NOCOUNT ON;

DECLARE
    @ACounter INT = 1,
    @AMax INT = 0,
    @BCounter INT = 1,
    @BMax INT = 0,
    @selected_A_Id int,
    @selected_B_Id int

Declare @A TABLE (
    [ID] int identity,
    A_Id int
)

Declare @B TABLE (
    [ID] int identity,
    B_Id int
)

INSERT INTO @A (A_Id)
SELECT A_Id FROM Table_A

SELECT @AMax = COUNT(Id) FROM @A

-- Loop A
WHILE @ACounter <= @AMax
BEGIN

    select @selected_A_Id = A_Id FROM @A WHERE ID=@ACounter
    Delete FROM @B;

    INSERT INTO @B (B_Id)
    SELECT B_Id  FROM Table_B WHERE (FK_A_ID = @selected_A_Id)

    SELECT @BMax = COUNT(ID) FROM @B
    set @BCounter = 1

    -- Loop B
    WHILE @BCounter <= @BMax
    BEGIN

        select @selected_B_Id = B_Id FROM @B WHERE ID=@BCounter;
        -- here I want to do something with  @selected_B_Id
        -- but @selected_B_Id gets set to the B_Id of the first record of table B and stays the same
        -- throughout both loops

        SET @BCounter = @BCounter + 1
    END

SET @ACounter = @ACounter + 1
END

END

the declared variable @selected_B_Id should be set to a value from table B which is filled with records in loop A. But for some reason the value of @selected_B_Id does not change while looping through both loops.

I placed the declaration of @selected_B_Id inside loop B but that did not change the outcome.

Could someone please help me to get this correct?

Answer 1

The for xml solution (that has been replaced by string_agg in 2017 onwards) looks like this:

declare @person table(pid int, pname varchar(100));
declare @transaction table(pid int, skuid int);
declare @sku table(skuid int, skuname varchar(100));

insert into @person values(1,'Person 1'),(2,'Person 2'),(3,'Person 3')
insert into @transaction values(1,1),(1,2),(1,3),(1,4),(2,2),(2,4);
insert into @sku values(1,'Prod 1'),(2,'Prod 2'),(3,'Prod 3'),(4,'Prod 4'),(5,'Prod 5'),(6,'Prod 6');

select p.pid
      ,p.pname
      ,stuff((select ', ' + s.skuname
              from @transaction as t
                  join @sku as s
                    on t.skuid = s.skuid
              where p.pid = t.pid
              order by s.skuname
              for xml path('')
             ),1,2,'') as ReportField
from @person as p
group by p.pid
        ,p.pname
order by p.pname;

Output

+-----+----------+--------------------------------+
| pid |  pname   |          ReportField           |
+-----+----------+--------------------------------+
|   1 | Person 1 | Prod 1, Prod 2, Prod 3, Prod 4 |
|   2 | Person 2 | Prod 2, Prod 4                 |
|   3 | Person 3 | NULL                           |
+-----+----------+--------------------------------+