I'm taking a bit of creative license with the term "modular". CTEs and views can help with this, and are closer to true modules.
I like to encapsulate inside of stored procedures, and write my code in such a way that it reads almost like a a procedural language. Heavy use of indentation, subqueries, and temp tables lets you carry results through the proc. I like to hang conditions off of joins, so related logic is in the same place.
