When implementing some complex logic in SQL try first to think in terms of sets and relations.
Think of procedural way only when you definately cannot manage only with sets and relations. Using analytic functions and simple relational operators leads to more straightforward and error-free way of doing tough things then writing complex stored procedures.
Plus debugging stored procedures is not always convinient when dealing with large amounts of data.