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?

I think first and foremost you should be asking whether there is a solution to your problem that doesn't require one loop, let alone a nested one. What is this stored procedure actually doing? — iamdave, Apr 25 '19 at 14:41
I have 2 related tables (A:B, 1:n) and I want to collect (concatenate) string values from a field in table B and place it into a field in table A. — Manu, Apr 25 '19 at 14:46
Firstly, why? The smells like a *terrible* design choice. And secondly, that can be achieved without loops using `for xml` — iamdave, Apr 25 '19 at 14:52
On one of my web pages I want to show a summary of technical information about a company (lots of tables with 1:n relationships). Pulling this information into one page slows down loading; Since these data are not changing very frequently, I thought, let a background task 'conatenate' all this into a 'report-field' which then can be loaded into the page. — Manu, Apr 25 '19 at 15:01

score 1 · Accepted Answer · answered Apr 25 '19 at 15:17

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                           |
+-----+----------+--------------------------------+

I also found this: https://stackoverflow.com/questions/31211506/how-stuff-and-for-xml-path-work-in-sql-server. Together with iamdave's answer I think I can go ahead; thanks! — Manu, Apr 25 '19 at 15:20

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

1 Answers1

Output