在动态SQL中增加INT类型的参数(Incrementing a parameter of type INT in dynamic SQL)

在动态SQL中增加INT类型的参数(Incrementing a parameter of type INT in dynamic SQL)

我正试图在我的脚本中将每个循环中的参数集增加1。

这是我在脚本中做的一个例子：

DECLARE @I AS INT; SET @I = 0; DECLARE @SQL NVARCHAR(MAX) SET @SQL = 'WHILE '+ Convert(Varchar, @I) +' < (SELECT statement here...) BEGIN SET '+ Convert(Varchar, @I) +' = '+ Convert(Varchar, (@I + 1))' END'

这个脚本还有很多，但这是相关的部分。 我知道'+ Convert(Varchar, @I) +' I '+ Convert(Varchar, @I) +'只是将@I的值连接到字符串，但是任何人都可以提供任何关于如何使它的建议，以便@I的值增加1。

目前在执行sql时，set命令最终会如下所示：

SET 0 = 0 + 1

我需要它来改变下一个循环的实际变量值。

这有可能吗？

I'm trying to increment a parameter set within my script by 1 every loop of my while.

This is an example of what I'm doing within my script:

DECLARE @I AS INT; SET @I = 0; DECLARE @SQL NVARCHAR(MAX) SET @SQL = 'WHILE '+ Convert(Varchar, @I) +' < (SELECT statement here...) BEGIN SET '+ Convert(Varchar, @I) +' = '+ Convert(Varchar, (@I + 1))' END'

There is a lot more to this script but this is the relevant part. I understand that '+ Convert(Varchar, @I) +' is just going to concatenate the value of @I to the string, but could anyone offer any advice in how I can make it so that the value of @I is incremented by 1.

Currently when executing the sql, the set command will end up like the following:

SET 0 = 0 + 1

where as I need it to change the actual variables value for the next loop.

Is this even possible?

最满意答案

您可以在查询中使用'@I'作为变量：

我正试图在我的脚本中将每个循环中的参数集增加1。

这是我在脚本中做的一个例子：

DECLARE @SQL NVARCHAR(MAX) SET @SQL = ' DECLARE @I AS INT; SET @I = 0; WHILE @I +' < (SELECT statement here...) + ' BEGIN SET @I = @I + 1 END'

You can use the '@I' as a variable in your query:

I'm trying to increment a parameter set within my script by 1 every loop of my while.

This is an example of what I'm doing within my script:

DECLARE @SQL NVARCHAR(MAX) SET @SQL = ' DECLARE @I AS INT; SET @I = 0; WHILE @I +' < (SELECT statement here...) + ' BEGIN SET @I = @I + 1 END'