Using Temporary Procedures

I’ve often read in forums how people have special utility databases with all their stored procedures and functions for working on the databases on the server. It is great because you don’t want your utilities intruding into the actual databases that you are developing or testing.

The problem is that it doesn’t work. Let me demonstrate.

We’ll pretend that we have a database called ‘MyDevStuff’ with all our lovely tools. You’ll need to create this. Then we put in a dummy utility. In reality, it will be cunning routines for doing reports, checking for code smells, outputting the contents of tables, making metadata queries, creating documentation and so on.

Instead, we will create a single procedure that does nothing more than to report its database context, and we’ll run it on every database in the instance. At the same time, we’ll create an identical temporary procedure.

The result is

Well, to look on the positive side, the procedure was correct when it was in its own database. but if a procedure is executed, it uses its own context. However, a temporary procedure uses whatever context it is executed in. This can be very useful. I recently saw a comment on StackOverflow from a user who said that temporary Procedures had no apparent use. I don’t agree.

This means that you can only use temporary procedures or registered routines. I must explain that it is perfectly possible to create system procedures , functions and views. The problem with these is in deploying them. They are also intrusive into the master database, and you really ought to leave the master database alone. A lot of us use them but it is definitely a code smell.

Can one create a temporary procedure within a procedure? Then you can call an initialization routine and avoid having to put all the actual code of your routine in a batch every time you want to use it.

Yes. Contrary to all expectations, a temporary procedure is treated differently to a temporary table. It is not disposed of at the end of the procedure. The temporary table must be cleared away at the end of a procedure and you can see it happening here.

Because temporary procedures last for the entire session or connection, it means that you can use your initialization procedure to create the utilities you create at the start of your connection and they will continue to exist for the life of the connection. That procedure ‘InitStuff’ is permanent in your utilities database, and by calling it you either get your temporary procedures updated or created.

As always, there is a snag. You can’t create temporary functions or views. It is just procedures.

Aha! (I hear you say), all you need to do is to employ the USE command at the start of the function and specify the database context that you want the body of the routine to execute in.

Just try it!

I’ve found myself using temporary procedures quite a lot for tasks that require scripting across databases. I do it because it is less intrusive and requires less tear-down. Temporary procedures vanish without trace when the connection or session is terminated. In code, It is possible to create them within a session, use them and then close the connection. It would be possible to use prepared statements but these would have to be referenced by a handle in a variable, and finally the handle would have to be disposed of: That is an unpleasant complication.

There is an irritating problem with any temporary routine: It means updating the scripts, applications and files whenever you change or improve the procedure. It isn’t a good idea to have more than one source. If you maintain these from an initialization procedure held anywhere on the instance, then this problem is contained: You just alter the initialization procedure in your utility database from a single source in source-control.

A slick way around all this is to create pretend system views, functions or procedures. This is too intrusive and is seldom allowed. Imagine wanting to do it on a production server!

Here is an example of the sort of utility that you might want as a temporary procedure whilst developing code. You can just create it in your current query window in SSMS. From then on you can use it for creating temporary variables until you close the query  

You could then execute this as

Yes, I know you’d be very unlikely to want to use this query: it is just an example! If you did, you would see, and then be able to use, This.

… which you could execute successfully …