Blog Post

Scripting with a temp stored procedure

,

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.

Original post (opens in new tab)
View comments in original post (opens in new tab)

Rate

You rated this post out of 5. Change rating

Share

Share

Rate

You rated this post out of 5. Change rating