Looping through records to update

Transact-SQL
1

Hello,

I have a list of records that I need to loop through and update based on values of other fields in the same table.

I'm trying to do something like this

UPDATE Reviews SET Correct_Actions = dbo.fncUpdateCorrect_Actions(@ReviewID, @Score_CorrectID, @Score_MiniMiranda)
WHERE ReviewID = @ReviewID

CREATE FUNCTION fncUpdateCorrect_Actions
(
@ReviewID Int = NULL
,@Score_CorrectID bit = NULL
,@Score_ProperlyIdentified bit = NULL
,@Score_MiniMiranda bit = NULL
)
RETURNS int
AS
BEGIN
DECLARE @Correct_Actions int

SET @Correct_Actions = 
  (CASE @Score_CorrectID WHEN 1 THEN 1 ELSE 0 END +  
  CASE @Score_ProperlyIdentified WHEN 1 THEN 1 ELSE 0 END +
  CASE @Score_MiniMiranda WHEN 1 THEN 1 ELSE 0 END);

  RETURN @Correct_Actions

END

I have several questions
-How do pass the parameter values to the function?

  • I have the logic for calculating the Correct_Actions field in a scalar function. Is there a better way to do this?
  • How do loop through a bunch of records to update them all?

I'm open to any new approaches. Thanks

2

A function cannot update data.

You need a Stored procedure for that. OTOH, It would be more performant if you did it all in a single UPDATE query, basically letting SQL do the looping

1 Like
3

Yes, forget looping, set-based will be much better. For example, something like below: this code won't be quite right, but it should give you an idea of what needs done.

UPDATE r
SET Correct_Actions = 
    CASE WHEN ts.Score_CorrectID = 1 THEN 1 ELSE 0 END +  
    CASE WHEN ts.Score_ProperlyIdentified = 1 THEN 1 ELSE 0 END +
    CASE WHEN ts.Score_MiniMiranda = 1 THEN 1 ELSE 0 END
FROM Reviews r
INNER JOIN table_that_contains_score_corrections ts ON ts.ReviewID = r.ReviewID
1 Like