SQL Server stored procedure with while condition containing table variable

Question

I have a table where the name of the country changes regularly, like my_table_US_NA, my_table_CAN_NA, my_table_MEX_NA and so on:

create table my_table_US_NA(id int)
insert into my_table_US_NA(id) values (1)
insert into my_table_US_NA(id) values (2)
insert into my_table_US_NA(id) values (3)
insert into my_table_US_NA(id) values (4)

select * from my_table_US_NA

id
----
 1
 2
 3
 4

I have a stored procedure like this:

create procedure my_looping_procedure (@Country varchar(10))
as
begin
    declare @MyTable varchar(50), @COUNTER int

    set @COUNTER = 1
    set @MyTable = concat('my_table_', @Country, '_NA')

    while (@COUNTER <= (select max(id) from @MyTable))
    begin
        set @COUNTER = @COUNTER + 1
        print @COUNTER
    end
end

When I try to compile the procedure, I get this error:

Msg 1087, Level 16, State 1, Procedure my_looping_procedure, Line 15 [Batch Start Line 0]
Must declare the table variable "@MyTable"

I tried moving the while loop into its own little variable:

create procedure my_looping_procedure (@Country varchar(10))
as
begin
    declare @MyTable varchar(50),
            @sql_loop varchar(max),
            @COUNTER int

    set @COUNTER = 1
    set @MyTable = concat('my_table_', @Country, '_NA')

    -- inner variable here
    select @sql_loop = '
while (' + @COUNTER + '<= (select max(id) from ' + @MyTable + '))
    begin
        set ' + @COUNTER + ' = ' + @COUNTER + ' + 1
        print ' + @COUNTER + '
    end'

    exec(@sql_loop)
end

That compiles but returns an error when I try to execute it exec my_looping_procedure:

Msg 245, Level 16, State 1, Procedure my_looping_procedure, Line 16 [Batch Start Line 26]
Conversion failed when converting the varchar value 'WHILE (' to data type int.

I tried declaring and setting all the variables inside @sql_loop:

alter procedure my_looping_procedure (@Country varchar(10))
as
begin
    declare @sql_loop varchar(max)

    select @sql_loop = '
declare 
@MyTable varchar(50),
@COUNTER INT
SET @COUNTER = 1
set @MyTable = concat(''my_table_'', ' + @Country + ', ''_NA'')

WHILE (@COUNTER <= (select max(id) from ' + @MyTable + '))
    BEGIN   
        SET @COUNTER = @COUNTER + 1
        print @COUNTER
    end'
exec(@sql_loop)
end

This compiles but still errors on execution:

Msg 1087, Level 16, State 1, Line 38
Must declare the table variable "@MyTable".

I then declared the @MyTable variable in the beginning again:

alter procedure my_looping_procedure (@Country varchar(10))
as
begin
    declare 
            @MyTable varchar(50),
            @sql_loop varchar(max)

    set @MyTable = concat('my_table_', @Country, '_NA')

    select @sql_loop = '
declare 
@MyTable varchar(50),
@COUNTER INT,
@Country varchar(10),
SET @COUNTER = 1
set @MyTable = concat(''my_table_'', ' + @Country + ', ''_NA'')

WHILE (@COUNTER <= (select max(id) from ' + @MyTable + ' ))
    BEGIN   
        SET @COUNTER = @COUNTER + 1
        print @COUNTER
    end'

    exec(@sql_loop)
end

This actually compiles but complains about the country:

Msg 207, Level 16, State 1, Line 37
Invalid column name 'US'.

Finally, I commented out the initial table set statement:

alter procedure my_looping_procedure (@Country varchar(10))
as
begin
    declare
            @MyTable varchar(50),
            @sql_loop varchar(max)
    -- set @MyTable = concat('my_table_', @Country, '_NA')

    select @sql_loop = '
declare 
@MyTable varchar(50),
@COUNTER INT,
@Country varchar(10),
@MaxCount int
SET @COUNTER = 1
set @MyTable = concat(''my_table_'', ' + @Country + ', ''_NA'')

WHILE (@COUNTER <= (select max(id) from ' + @MyTable + ' ))
    BEGIN   
        SET @COUNTER = @COUNTER + 1
        print @COUNTER
    end'

    exec(@sql_loop)
end

This compiles AND runs, but does nothing.

Can anybody figure out what I'm doing wrong?

Some background: This is an example of the problem with the parameter and the while loop, not the actual code. As for why it's done this way, the initial design was just for one hard-coded country. When more countries were added, the scripts were copied with new countries hard-coded. The initial designer is no longer with the company. My current task is just to make a generic piece of code that can be used no matter how many more countries we add. There are hundreds of scripts like this and very little time and few resources on the project.

I genuinely appreciate the suggestions of using a temp table, but the tables are used in other processes. Until we iron out the underlying issues with the process, we are stuck with this design.

Answer 1

Without questioning why you are doing it this way (but those comments are very useful and should be carefully considered). Here is your working code:

create table #my_table_US_NA(id int);

insert into #my_table_US_NA(id) values (1),(2),(3),(4);

declare @MyTable varchar(50), @Country varchar(10);
set @Country = 'US';
set @MyTable = quotename(concat('#my_table_', @Country, '_NA'));

declare @Sql nvarchar(max) = 'declare @COUNTER INT = 1; WHILE (@COUNTER <= (select max(id) from [' + @MyTable + ']))
    BEGIN   
        SET @COUNTER = @COUNTER + 1
        print @COUNTER
    end';

exec(@Sql);

drop table #my_table_US_NA;

Note 1: I've added quotename as per Larnu's suggestion to avoid the possibility of injection.

Note 2: Your table design doesn't align with how relational databases are intended to be used. You wouldn't normally have a separate table for each country, you would normally have a country column which allows you to segment the table by country. No good design should end up relying on dynamic SQL, sure you might need it for some edge cases but not your main business flow.