Loop in stored procedure in SQL server

Question

Writing a stored procedure that calls another stored procedure and provides values to it is something I need assistance with. To transfer this from C# to a stored procedure and create a SQL agent task that calls the stored procedure at a certain time is what I want to do now. Any thoughts? This holds true.

Table A:

PK_TableA_ID

Table B:

PK_TableB_ID

Stored procedure SP1:

@TableA_ID
@TableB_ID

I need this but in T-SQL

foreach(var TableAId in TableA)
{
foreach(var TableBId in TableB)
{
//call stored procedure 
SP1(TableAId, TableBId);
}
}

Can someone please help me with this?

Answer 1

Here is an illustration of a loop that uses cursors:

-- set up some test data
declare @table_a table (PK_TableA_ID int)
declare @table_b table (PK_TableB_ID int)
insert @table_a values (1),(2),(3)
insert @table_b values (4),(5),(6)    

-- do the actual processing
SET NOCOUNT ON

DECLARE @TableA_ID int, @TableB_ID int

DECLARE TableA_cursor CURSOR FOR SELECT PK_TableA_ID FROM @table_a

OPEN TableA_cursor
FETCH NEXT FROM TableA_cursor INTO @TableA_ID

WHILE @@FETCH_STATUS = 0
BEGIN
    DECLARE TableB_cursor CURSOR FOR SELECT PK_TableB_ID FROM @table_b

    OPEN TableB_cursor
    FETCH NEXT FROM TableB_cursor INTO @TableB_ID

    WHILE @@FETCH_STATUS = 0
    BEGIN
        PRINT CAST(@TableA_ID AS CHAR(1)) + ':' + CAST(@TableB_ID AS CHAR(1))
        -- execute your stored procedure here:
        -- EXEC Your_stored_procedure (@TableA_ID, @TableB_ID) 
        FETCH NEXT FROM TableB_cursor INTO @TableB_ID
        END

    CLOSE TableB_cursor
    DEALLOCATE TableB_cursor

    FETCH NEXT FROM TableA_cursor INTO @TableA_ID
END 
CLOSE TableA_cursor
DEALLOCATE TableA_cursor

The cursor above (with the test data in the temporary tables) will generate this output:

1:4
1:5
1:6
2:4
2:5
2:6
3:4
3:5
3:6

However, using cursors might not be the greatest solution to your issue.

Answer 2

you can use while loop

While (condition)

Begin

End