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.