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
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
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