TSQL可供光标在更新触发器数据上循环使用

Question

在这种情况下的答案中，

有人建议，由于性能原因，我不应该使用游标。在更新触发器中循环更新数据的最佳做法是什么？

更新：

以下是用于创建该更新触发器的TSQL。

CREATE TRIGGER [dbo].[trAfterUpdateInfoDoc]
ON [dbo].[InfoDocs]
AFTER UPDATE
AS
BEGIN
DECLARE @infodoctemplateid INT;
DECLARE @infodocid INT;
DECLARE @requireccount FLOAT(2);
DECLARE @filledcount FLOAT(2);
DECLARE @pcnt FLOAT(2);
DECLARE c CURSOR FOR
SELECT id 
FROM InfoDocs ifd 
WHERE exists (SELECT 1 FROM Inserted AS i WHERE i.id = ifd.id)
OPEN c
FETCH NEXT FROM c INTO @infodocid
WHILE @@Fetch_Status = 0 
BEGIN
SELECT @infodoctemplateid = InfoDocTemplateId 
FROM InfoDocs 
WHERE id = @infodocid;
SELECT @requireccount = COUNT(*) 
FROM InfoDocTemplateFields 
WHERE InfoDocTemplateId = @infodoctemplateid 
AND IsRequired = 1;
IF (@requireccount = 0)
BEGIN
set @pcnt = 100;
END
ELSE
BEGIN
select @filledcount = count(*) from InfoDocFields 
where InfoDocId = @infodocid 
and InfoDocTemplateFieldId in (select id from InfoDocTemplateFields where InfoDocTemplateId = @infodoctemplateid and IsRequired = 1)
and (BooleanValue is not null or (StringValue is not null and StringValue <> '') or IntValue is not null or DateValue is not null)
set @pcnt = @filledcount / @requireccount * 100.0;
END
update InfoDocs set PercentageCompleted = @pcnt Where id = @infodocid;
Fetch next From c into @infodocid
End
Close c
Deallocate c
END

Answer 1

我曾尝试将您的光标翻译成基于集合的代码，但我无法测试我的解决方案是否正确，而且昨晚我没有睡太多觉，所以我可能会错过一些东西——它可能比我写的代码更短、更高效，但它应该给您一个很好的起点：

CREATE TRIGGER [dbo].[trAfterUpdateInfoDoc]
ON [dbo].[InfoDocs]
AFTER UPDATE
AS
BEGIN
WITH CTE1 AS
(
SELECT  ifd.Id, 
SUM(CASE WHEN IsRequired = 1 THEN 1 ELSE 0 END) As RequiredCount,
(
select count(*) 
from InfoDocFields 
where InfoDocFields.InfoDocId = ifd.Id,
and InfoDocTemplateFieldId in (
select id 
from InfoDocTemplateFields 
where InfoDocTemplateId = idtf.InfoDocTemplateId 
and IsRequired = 1
)
and 
InfoDocFields.BooleanValue is not null 
or (InfoDocFields.StringValue is not null and InfoDocFields.StringValue <> '') 
or InfoDocFields.IntValue is not null 
or InfoDocFields.DateValue is not null
) As Filledcount
FROM InfoDocs ifd 
JOIN InfoDocTemplateFields idtf
ON ifd.InfoDocTemplateId = idtf.InfoDocTemplateId
WHERE exists (SELECT 1 FROM Inserted AS i WHERE i.id = ifd.id)
GROUP BY ifd.Id, idtf.InfoDocTemplateId
), CTE2 AS
(
SELECT  ifd.Id, 
CASE WHEN RequiredCount = 0 THEN 
100
ELSE
Filledcount / RequiredCount * 100.0
END As Completed
FROM CTE1
)
UPDATE docs 
SET PercentageCompleted = Completed 
FROM InfoDocs docs
JOIN cte2 
ON docs.id = cte2.Id
END

Answer 2

您可以通过使用联接进行更新来消除光标。

例如

UPDATE t1
SET Col2 = t2.Col2,
Col3 = t2.Col3
FROM Table1 t1
INNER JOIN Table2 t2 ON t1.Col1 = t2.Col1
WHERE t1.Col1 IN (21, 31)

这将为您带来尽可能好的性能。代码将更加紧凑，更易于理解。