Simon Harriyott

Numbered Stored Procedures

I've been looking at some legacy code recently, and came across something completely new to me; numbered stored procedures. It turns out that in the old days, you could create several stored procedures with the same name, and give each a different number. As you can see from the MS documentation, there is a number parameter.

These are called in the normal way:

EXEC UpdateOrder;4 20, 39
EXEC UpdateOrder;7 '12 High St.'

It's not very nice, and the "advantage" of being able to DROP them all in one go (the only feature mentioned in the documentation), isn't worth the diminished readability.

Even worse though, is that the newer SQL Server Management Studio doesn't show them in the tree view, and thus can't open them. Even trying to view the code by using INFORMATION_SCHEMA only shows the first one. I'm sure the old Enterprise Manager must be able to, but I don't want to have to install it especially.

Anyway, I thank my colleagues from throughout my career for having kept me from needing to know about this until now. I don't know who introduced this nonsense into this project (or indeed, SQL Server), and I hope it remains thus.

13 February 2009