Kenneth Fisher has a use for temporary stored procedures:
The other day I was asked to create a SQL Audit on several different databases. Completely unexpectedly (sarcasm warning!) the list grew, not once, not twice, but enough times that I’ve lost count, and each time I would copy and paste my code for the new databases and change the database name in each piece. Then on one notable occasion I had to change the code for each of the, at that point 10, copies of the code. Talk about a headache.
Then there was the epiphany.