How to do for each using inside stored procedure

Question

I have two tables t1 and t2

t1 have 19 records and i need to pass one by one t1 Name and id to get count by name from t2 and later update into again t1 table counts, below i have wrote but it not working for me below things wrote into stored procedure

declare @count varchar(500)

declare @Name varchar(50),@id int

select @Name=Name,@id=id from t1 where IsActive=1 //this line need to pass loop to below two lines

select @count=count(distinct T.Id) from t2 (NOLOCK) T where T.EMR like '%'+@Name+'%'

update t1 set TroveTotal=@count where id=@Id

Answer 1

Thanks for posting your question in the Microsoft Q&A forum.

You can achieve this with a JOIN. It updates the TroveTotal column in t1 with the counts obtained from the subquery

CREATE PROCEDURE UpdateT1WithCounts
AS
BEGIN
    UPDATE t1
    SET TroveTotal = ISNULL(subquery.Count, 0)
    FROM t1
    LEFT JOIN (
        SELECT t1.id, COUNT(DISTINCT t2.id) AS Count
        FROM t1
        LEFT JOIN t2 ON t2.EMR LIKE '%' + t1.Name + '%'
        WHERE t1.IsActive = 1
        GROUP BY t1.id
    ) AS subquery ON t1.id = subquery.id
END

---

Please don't forget to close up the thread here by upvoting and accept it as an answer if it is helpful