I saw Riley Major speak on refactoring monolith stored procedures on Tuesday night at PASSMN.

Things like adding RAND() which give a random decimal or more likely something like DATEPART(hour, GETDATE()) which more usefully gives you the current hour when tossed in the mix make a sproc nondeterministic as if something is deterministic the same inputs should always yield that same outputs, predictably. The sort of external dependencies along the lines of sending emails from SQL or running CLR from SQL are also of this troublesome ilk. These issues will be mixed in with everything else and they need to be abstracted into isolation in a refactoring. These need to be pulled out to variables and mocked. The way this stuff can be "mocked" in test is by jamming settings for the variables into the sproc after their normal assignments temporarily. If this sounds hacky this is why people mostly don't test sprocs, but you don't really have a choice when refactoring a monolith stored procedure do you? You can't just make changes as you'll break something. Build a "ghost house" with variables to mock your black boxes. Look for anything that potentially changes (INSERT, UPDATE, DELETE) to know all tables that are affected and hoist DECLARE statements to the top as much as possible. Sometimes you won't be able to move the assignments up. Other things said? Dynamic SQL should be used sparingly. In this approach you are building a SQL statement inside SQL in a string and then using EXEC (@yourNameHere) to run the SQL you have made, in T-SQL at least. SELECT CONVERT(GETDATE(), SYSDATETIMEOFFSET()) should explicitly show the time zone offset. UDF stands for user defined functions and when these return tables you basically have a view with parameters. The PASS part of PASSMN stands for Professional Association for SQL Server and, less cryptically, the MN stands for Minnesota. This thing met at the Microsoft office in Edina. Before Riley Major spoke an Andrea Imhoff gave a smaller talk on being an analyst designing reports and in posing the question "How do you make the space for yourself to participate as a partner in analytical and reporting work?" got audience members to suggest that each business unit has its own reporting requirements and that there is no reporting template. Andrea is pictured below with Dan English who seemed to be the moderator for PASSMN.