带有包含表变量的 while 条件的 SQL Server 存储过程 [英] SQL Server stored procedure with while condition containing table variable

问题描述

我有一个国家名称定期更改的表格，例如 my_table_US_NA、my_table_CAN_NA、my_table_MEX_NA 等:

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:

消息 1087，级别 16，状态 1，过程 my_looping_procedure，第 15 行 [批处理起始行 0]
必须声明表变量@MyTable"

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

我尝试将 while 循环移动到它自己的小变量中:

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

编译但在我尝试执行时返回错误exec my_looping_procedure:

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

消息 245，级别 16，状态 1，过程 my_looping_procedure，第 16 行 [批处理开始第 26 行]
将 varchar 值 'WHILE (' 转换为数据类型 int 时转换失败.

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.

我尝试在 @sql_loop 中声明和设置所有变量:

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:

消息 1087，级别 16，状态 1，第 38 行
必须声明表变量@MyTable".

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

然后我又在开头声明了@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:

消息 207，级别 16，状态 1，第 37 行
无效的列名US".

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

最后，我注释掉了初始 table set 语句:

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?

一些背景:这是参数和 while 循环问题的示例，而不是实际代码.至于为什么这样做，最初的设计只是针对一个硬编码的国家.当添加更多国家时，脚本被复制到硬编码的新国家.最初的设计师不再在公司工作.我目前的任务只是编写一段通用代码，无论我们添加多少个国家，都可以使用它.有数百个这样的脚本，而且项目上的时间和资源很少.

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.

推荐答案

不用问你为什么要这样做(但这些评论非常有用，应该仔细考虑).这是您的工作代码:

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;

注 1:我已经按照 Larnu 的建议添加了 quotename 以避免注入的可能性.

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

注意 2:您的表设计与关系数据库的使用方式不一致.您通常不会为每个国家/地区提供单独的表格，您通常会有一个国家/地区列，可让您按国家/地区对表格进行细分.任何好的设计都不应该最终依赖于动态 SQL，当然您可能需要它来处理某些边缘情况，而不是您的主要业务流程.

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.

这篇关于带有包含表变量的 while 条件的 SQL Server 存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！