March 17, 2022 by Kenneth Fisher
A few years back I learned about temporary stored procedures from a Kendra Little (blog|twitter) blog post. At the time the only use I had for them (and it’s a pretty spectacular use) was performance tuning. Specifically because variables and parameters are not the same to the compiler. Regardless, the other day I found a fantastic (IMO) use for them. Just like any other stored procedure, repeatable code!
CREATE PROCEDURE #TempSP (@DBName sysname) AS BEGIN DECLARE @SQL nvarchar(max); /* Code to do something. Say create a SQL Audit. */ SET @SQL = N'PRINT ''We did work on: ' + QUOTENAME(@DBName,'[') + N''''; EXEC sp_executesql @SQL; END GO EXEC #TempSP 'DB1'; EXEC #TempSP 'DB2'; EXEC #TempSP 'DB3'; EXEC #TempSP 'DB4'; EXEC #TempSP 'DB5'; EXEC #TempSP 'DB6';
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. Suddenly I realized I was using repeatable code. I.e. the exact reason that stored procedures were created. Our internal procedures would have made it a headache to put my own SPs (even temporarily) into our production user databases so what could I do? A temporary stored procedure! They don’t go into a production database, they go into tempdb.
The more I looked at the end result the happier I was. Add a new database? No problem, just add a new call at the bottom. Need to make a change to the code? No problem. It’s a SP. I just change it in the one place. And when I’m done, I close the connection and the SP is no longer on the instance. I can’t tell you how much easier this has made my life.