Tsql Alternative For Cursor To Loop Over Update-trigger Data

In the answers on this case it was suggested that I should not use cursor because of performance reasons. What are the best practices to loop over the update data in an update tri

Solution 1:

I've tried translating your cursor into a set based code, however there is no way for me to test if my solution is correct, and I didn't get much sleep last night so I might have missed some things here and there - and it probably can be a shorter and more efficient code than what I've written, but it should give you a good place to start:

CREATE TRIGGER [dbo].[trAfterUpdateInfoDoc]
ON [dbo].[InfoDocs]
AFTER UPDATE
AS
BEGIN
    WITH CTE1 AS
    (
        SELECT  ifd.Id, 
                SUM(CASEWHEN IsRequired = 1THEN1ELSE0END) 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 isnot null 
                        or (InfoDocFields.StringValue isnot null and InfoDocFields.StringValue <> '') or InfoDocFields.IntValue isnot null 
                        or InfoDocFields.DateValue isnot null

                ) As Filledcount
        FROM InfoDocs ifd 
        JOIN InfoDocTemplateFields idtf
            ON ifd.InfoDocTemplateId = idtf.InfoDocTemplateId
        WHERE exists (SELECT1FROM Inserted AS i WHERE i.id = ifd.id)
        GROUPBY ifd.Id, idtf.InfoDocTemplateId
    ), CTE2 AS
    (
        SELECT  ifd.Id, 
                CASEWHEN RequiredCount = 0THEN100ELSE
                    Filledcount / RequiredCount * 100.0ENDAs Completed
        FROM CTE1
    )

    UPDATE docs 
    SET PercentageCompleted = Completed 
    FROM InfoDocs docs
    JOIN cte2 
        ON docs.id = cte2.Id

END

Solution 2:

You can get rid of the cursor by doing an update with a join.

E.g.

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

This will get you the best possible performance. And the code will be more compact and easier to understand.