假设我有以下简单的表变量:
declare @databases table
(
DatabaseID int,
Name varchar(15),
Server varchar(15)
)
-- insert a bunch rows into @databases
如果我想遍历各行,声明和使用游标是我唯一的选择吗?还有别的办法吗?
假设我有以下简单的表变量:
declare @databases table
(
DatabaseID int,
Name varchar(15),
Server varchar(15)
)
-- insert a bunch rows into @databases
如果我想遍历各行,声明和使用游标是我唯一的选择吗?还有别的办法吗?
当前回答
我更喜欢使用偏移获取,如果你有一个唯一的ID,你可以排序你的表:
DECLARE @TableVariable (ID int, Name varchar(50));
DECLARE @RecordCount int;
SELECT @RecordCount = COUNT(*) FROM @TableVariable;
WHILE @RecordCount > 0
BEGIN
SELECT ID, Name FROM @TableVariable ORDER BY ID OFFSET @RecordCount - 1 FETCH NEXT 1 ROW;
SET @RecordCount = @RecordCount - 1;
END
这样我就不需要向表中添加字段或使用窗口函数。
其他回答
简单说明一下,如果你使用的是SQL Server(2008及以上版本),下面的例子有:
While (Select Count(*) From #Temp) > 0
会不会更好
While EXISTS(SELECT * From #Temp)
Count将不得不触及表中的每一行,EXISTS只需要触及第一行。
我真的不明白为什么你需要使用可怕的光标。 但是如果你使用的是2005/2008版本的SQL Server,还有另一种选择 使用递归
declare @databases table
(
DatabaseID int,
Name varchar(15),
Server varchar(15)
)
--; Insert records into @databases...
--; Recurse through @databases
;with DBs as (
select * from @databases where DatabaseID = 1
union all
select A.* from @databases A
inner join DBs B on A.DatabaseID = B.DatabaseID + 1
)
select * from DBs
我更喜欢使用偏移获取,如果你有一个唯一的ID,你可以排序你的表:
DECLARE @TableVariable (ID int, Name varchar(50));
DECLARE @RecordCount int;
SELECT @RecordCount = COUNT(*) FROM @TableVariable;
WHILE @RecordCount > 0
BEGIN
SELECT ID, Name FROM @TableVariable ORDER BY ID OFFSET @RecordCount - 1 FETCH NEXT 1 ROW;
SET @RecordCount = @RecordCount - 1;
END
这样我就不需要向表中添加字段或使用窗口函数。
我同意之前的帖子,基于集的操作通常会执行得更好,但如果你确实需要遍历行,下面是我将采取的方法:
Add a new field to your table variable (Data Type Bit, default 0) Insert your data Select the Top 1 Row where fUsed = 0 (Note: fUsed is the name of the field in step 1) Perform whatever processing you need to do Update the record in your table variable by setting fUsed = 1 for the record Select the next unused record from the table and repeat the process DECLARE @databases TABLE ( DatabaseID int, Name varchar(15), Server varchar(15), fUsed BIT DEFAULT 0 ) -- insert a bunch rows into @databases DECLARE @DBID INT SELECT TOP 1 @DBID = DatabaseID from @databases where fUsed = 0 WHILE @@ROWCOUNT <> 0 and @DBID IS NOT NULL BEGIN -- Perform your processing here --Update the record to "used" UPDATE @databases SET fUsed = 1 WHERE DatabaseID = @DBID --Get the next record SELECT TOP 1 @DBID = DatabaseID from @databases where fUsed = 0 END
-- [PO_RollBackOnReject] 'FININV10532'
alter procedure PO_RollBackOnReject
@CaseID nvarchar(100)
AS
Begin
SELECT *
INTO #tmpTable
FROM PO_InvoiceItems where CaseID = @CaseID
Declare @Id int
Declare @PO_No int
Declare @Current_Balance Money
While (Select ROW_NUMBER() OVER(ORDER BY PO_LineNo DESC) From #tmpTable) > 0
Begin
Select Top 1 @Id = PO_LineNo, @Current_Balance = Current_Balance,
@PO_No = PO_No
From #Temp
update PO_Details
Set Current_Balance = Current_Balance + @Current_Balance,
Previous_App_Amount= Previous_App_Amount + @Current_Balance,
Is_Processed = 0
Where PO_LineNumber = @Id
AND PO_No = @PO_No
update PO_InvoiceItems
Set IsVisible = 0,
Is_Processed= 0
,Is_InProgress = 0 ,
Is_Active = 0
Where PO_LineNo = @Id
AND PO_No = @PO_No
End
End