Scripting with a temp stored procedure


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)
	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;

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.

2 thoughts on “Scripting with a temp stored procedure

  1. […] Kenneth Fisher has a use for temporary stored procedures: […]

  2. twoknightsthenight says:

    I’ve only got 24 or so databases in both test and production to set up for replication between Linux and Windows so this is probably a good time to try it out. (ProTip: don’t use different OS types with replication. Just don’t.)

    – Cujo

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 3,755 other subscribers

Follow me on Twitter

ToadWorld Pro of the Month November 2013
%d bloggers like this: