我可以遍历 T-SQL 中的表变量吗？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1578198/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Can I loop through a table variable in T-SQL?
提问by Kuyenda
Is there anyway to loop through a table variable in T-SQL?
无论如何要遍历 T-SQL 中的表变量？
DECLARE @table1 TABLE ( col1 int )
INSERT into @table1 SELECT col1 FROM table2
I use cursors as well, but cursors seem less flexible than table variables.
我也使用游标，但游标似乎不如表变量灵活。
DECLARE cursor1 CURSOR
FOR SELECT col1 FROM table2
OPEN cursor1
FETCH NEXT FROM cursor1
I would like to be able to use a table variable in the same manner as a cursor. That way I could execute some query on the table variable in one part of the procedure, and then later execute some code for each row in the table variable.
我希望能够以与游标相同的方式使用表变量。这样我就可以在过程的一部分中对表变量执行一些查询，然后稍后为表变量中的每一行执行一些代码。
Any help is greatly appreciated.
任何帮助是极大的赞赏。
回答by KM.
Add an identity to your table variable, and do an easy loop from 1 to the @@ROWCOUNT of the INSERT-SELECT.
为您的表变量添加一个标识，并执行从 1 到 INSERT-SELECT 的 @@ROWCOUNT 的简单循环。
Try this:
尝试这个：
DECLARE @RowsToProcess int
DECLARE @CurrentRow int
DECLARE @SelectCol1 int
DECLARE @table1 TABLE (RowID int not null primary key identity(1,1), col1 int )
INSERT into @table1 (col1) SELECT col1 FROM table2
SET @RowsToProcess=@@ROWCOUNT
SET @CurrentRow=0
WHILE @CurrentRow<@RowsToProcess
BEGIN
SET @CurrentRow=@CurrentRow+1
SELECT
@SelectCol1=col1
FROM @table1
WHERE RowID=@CurrentRow
--do your thing here--
END
回答by Justin Niessner
DECLARE @table1 TABLE (
idx int identity(1,1),
col1 int )
DECLARE @counter int
SET @counter = 1
WHILE(@counter < SELECT MAX(idx) FROM @table1)
BEGIN
DECLARE @colVar INT
SELECT @colVar = col1 FROM @table1 WHERE idx = @counter
-- Do your work here
SET @counter = @counter + 1
END
Believe it or not, this is actually more efficient and performant than using a cursor.
信不信由你，这实际上比使用游标更高效、更高效。
回答by typoerrpr
My two cents.. From KM.'s answer, if you want to drop one variable, you can do a countdown on @RowsToProcess instead of counting up.
我的两分钱......从 KM. 的回答中，如果你想删除一个变量，你可以对 @RowsToProcess 进行倒计时而不是向上计数。
DECLARE @RowsToProcess int;
DECLARE @table1 TABLE (RowID int not null primary key identity(1,1), col1 int )
INSERT into @table1 (col1) SELECT col1 FROM table2
SET @RowsToProcess = @@ROWCOUNT
WHILE @RowsToProcess > 0 -- Countdown
BEGIN
SELECT *
FROM @table1
WHERE RowID=@RowsToProcess
--do your thing here--
SET @RowsToProcess = @RowsToProcess - 1; -- Countdown
END
回答by Gildor
look like this demo:
看起来像这个演示：
DECLARE @vTable TABLE (IdRow int not null primary key identity(1,1),ValueRow int);
-------Initialize---------
insert into @vTable select 345;
insert into @vTable select 795;
insert into @vTable select 565;
---------------------------
DECLARE @cnt int = 1;
DECLARE @max int = (SELECT MAX(IdRow) FROM @vTable);
WHILE @cnt <= @max
BEGIN
DECLARE @tempValueRow int = (Select ValueRow FROM @vTable WHERE IdRow = @cnt);
---work demo----
print '@tempValueRow:' + convert(varchar(10),@tempValueRow);
print '@cnt:' + convert(varchar(10),@cnt);
print'';
--------------
set @cnt = @cnt+1;
END
Version without idRow, using ROW_NUMBER
没有 idRow 的版本，使用 ROW_NUMBER
DECLARE @vTable TABLE (ValueRow int);
-------Initialize---------
insert into @vTable select 345;
insert into @vTable select 795;
insert into @vTable select 565;
---------------------------
DECLARE @cnt int = 1;
DECLARE @max int = (select count(*) from @vTable);
WHILE @cnt <= @max
BEGIN
DECLARE @tempValueRow int = (
select ValueRow
from (select ValueRow
, ROW_NUMBER() OVER(ORDER BY (select 1)) as RowId
from @vTable
) T1
where t1.RowId = @cnt
);
---work demo----
print '@tempValueRow:' + convert(varchar(10),@tempValueRow);
print '@cnt:' + convert(varchar(10),@cnt);
print'';
--------------
set @cnt = @cnt+1;
END
回答by Raj More
You can loop through the table variable or you can cursor through it. This is what we usually call a RBAR - pronounced Reebar and means Row-By-Agonizing-Row.
您可以遍历表变量，也可以使用光标浏览它。这就是我们通常所说的 RBAR - 发音为 Reebar，意思是 Row-By-Agonizing-Row。
I would suggest finding a SET-BASED answer to your question (we can help with that) and move away from rbars as much as possible.
我建议为您的问题找到一个基于 SET 的答案（我们可以提供帮助）并尽可能远离 rbars。
回答by Philip Kelley
Here's my variant. Pretty much just like all the others, but I only use one variable to manage the looping.
这是我的变体。几乎就像所有其他人一样，但我只使用一个变量来管理循环。
DECLARE
@LoopId int
,@MyData varchar(100)
DECLARE @CheckThese TABLE
(
LoopId int not null identity(1,1)
,MyData varchar(100) not null
)
INSERT @CheckThese (MyData)
select MyData from MyTable
order by DoesItMatter
SET @LoopId = @@rowcount
WHILE @LoopId > 0
BEGIN
SELECT @MyData = MyData
from @CheckThese
where LoopId = @LoopId
-- Do whatever
SET @LoopId = @LoopId - 1
END
Raj More's point is relevant--only perform loops if you have to.
Raj More 的观点是相关的——只有在必要时才执行循环。
回答by Kuyenda
I didn't know about the WHILE structure.
我不知道 WHILE 结构。
The WHILE structure with a table variable, however, looks similar to using a CURSOR, in that you still have to SELECT the row into a variable based on the row IDENTITY, which is effectively a FETCH.
然而，带有表变量的 WHILE 结构看起来类似于使用 CURSOR，因为您仍然必须根据行 IDENTITY 将行 SELECT 到一个变量中，这实际上是一个 FETCH。
Is there any difference between using WHERE and something like the following?
使用 WHERE 和以下类似的东西有什么区别吗？
DECLARE @table1 TABLE ( col1 int )
INSERT into @table1 SELECT col1 FROM table2
DECLARE cursor1 CURSOR
FOR @table1
OPEN cursor1
FETCH NEXT FROM cursor1
I don't know if that's even possible. I suppose you might have to do this:
我不知道这是否可能。我想你可能必须这样做：
DECLARE cursor1 CURSOR
FOR SELECT col1 FROM @table1
OPEN cursor1
FETCH NEXT FROM cursor1
Thanks for you help!
谢谢你的帮助！
回答by Jeff Meatball Yang
Here's another answer, similar to Justin's, but doesn't need an identity or aggregate, just a primary (unique) key.
这是另一个答案，类似于 Justin 的答案，但不需要身份或聚合，只需要一个主（唯一）键。
declare @table1 table(dataKey int, dataCol1 varchar(20), dataCol2 datetime)
declare @dataKey int
while exists select 'x' from @table1
begin
select top 1 @dataKey = dataKey
from @table1
order by /*whatever you want:*/ dataCol2 desc
-- do processing
delete from @table1 where dataKey = @dataKey
end
回答by Gary Kindel
Here is my version of the same solution...
这是我的相同解决方案的版本...
declare @id int
SELECT @id = min(fPat.PatientID)
FROM tbPatients fPat
WHERE (fPat.InsNotes is not null AND DataLength(fPat.InsNotes)>0)
while @id is not null
begin
SELECT fPat.PatientID, fPat.InsNotes
FROM tbPatients fPat
WHERE (fPat.InsNotes is not null AND DataLength(fPat.InsNotes)>0) AND fPat.PatientID=@id
SELECT @id = min(fPat.PatientID)
FROM tbPatients fPat
WHERE (fPat.InsNotes is not null AND DataLength(fPat.InsNotes)>0)AND fPat.PatientID>@id
end
回答by Rahul Varadkar
Following Stored Procedure loop through the Table Variable and Prints it in Ascending ORDER. This example is using WHILE LOOP.
遵循存储过程循环遍历表变量并按升序打印它。此示例使用 WHILE LOOP。
CREATE PROCEDURE PrintSequenceSeries
-- Add the parameters for the stored procedure here
@ComaSeperatedSequenceSeries nVarchar(MAX)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
DECLARE @SERIES_COUNT AS INTEGER
SELECT @SERIES_COUNT = COUNT(*) FROM PARSE_COMMA_DELIMITED_INTEGER(@ComaSeperatedSequenceSeries, ',') --- ORDER BY ITEM DESC
DECLARE @CURR_COUNT AS INTEGER
SET @CURR_COUNT = 1
DECLARE @SQL AS NVARCHAR(MAX)
WHILE @CURR_COUNT <= @SERIES_COUNT
BEGIN
SET @SQL = 'SELECT TOP 1 T.* FROM ' +
'(SELECT TOP ' + CONVERT(VARCHAR(20), @CURR_COUNT) + ' * FROM PARSE_COMMA_DELIMITED_INTEGER( ''' + @ComaSeperatedSequenceSeries + ''' , '','') ORDER BY ITEM ASC) AS T ' +
'ORDER BY T.ITEM DESC '
PRINT @SQL
EXEC SP_EXECUTESQL @SQL
SET @CURR_COUNT = @CURR_COUNT + 1
END;
Following Statement Executes the Stored Procedure:
以下语句执行存储过程：
EXEC PrintSequenceSeries '11,2,33,14,5,60,17,98,9,10'
The result displayed in SQL Query window is shown below:
SQL Query 窗口中显示的结果如下所示：
The function PARSE_COMMA_DELIMITED_INTEGER() that returns TABLE variable is as shown below :
返回 TABLE 变量的函数 PARSE_COMMA_DELIMITED_INTEGER() 如下所示：
CREATE FUNCTION [dbo].[parse_comma_delimited_integer]
(
@LIST VARCHAR(8000),
@DELIMITER VARCHAR(10) = ',
'
)
-- TABLE VARIABLE THAT WILL CONTAIN VALUES
RETURNS @TABLEVALUES TABLE
(
ITEM INT
)
AS
BEGIN
DECLARE @ITEM VARCHAR(255)
/* LOOP OVER THE COMMADELIMITED LIST */
WHILE (DATALENGTH(@LIST) > 0)
BEGIN
IF CHARINDEX(@DELIMITER,@LIST) > 0
BEGIN
SELECT @ITEM = SUBSTRING(@LIST,1,(CHARINDEX(@DELIMITER, @LIST)-1))
SELECT @LIST = SUBSTRING(@LIST,(CHARINDEX(@DELIMITER, @LIST) +
DATALENGTH(@DELIMITER)),DATALENGTH(@LIST))
END
ELSE
BEGIN
SELECT @ITEM = @LIST
SELECT @LIST = NULL
END
-- INSERT EACH ITEM INTO TEMP TABLE
INSERT @TABLEVALUES
(
ITEM
)
SELECT ITEM = CONVERT(INT, @ITEM)
END
RETURN
END
