TransWikia.com

Numbered stored procedures: introduction and deprecation?

Database Administrators Asked by Jeroen Mostert on November 16, 2021

SQL Server and Sybase ASE both support the highly dubious feature of grouping stored procedures by appending a number:

CREATE PROCEDURE Foo;1 AS ...
CREATE PROCEDURE Foo;2 AS ...

The only tangible benefit is that DROP PROCEDURE Foo will drop all of these procedures together — and that’s it; to call them you still need to append the number explicitly. The drawbacks of allowing a semicolon to be part of a procedure name and having non-obvious DROP semantics should be obvious, and indeed many tools don’t (properly) support this format and things like contained databases outright forbid them.

In what version of SQL Server and/or Sybase ASE was this feature introduced, and from what version onwards was it deprecated? I’d consult the documentation, but documentation that goes back far enough does not appear to be available. I’ve found the following:

This leads me to think the feature might have existed in very early versions indeed, possibly before the split between Sybase and Microsoft, but still doesn’t tell me what the earliest introduction and deprecation was. The asteroid to kill this dinosaur is still in orbit, but how old is the dinosaur?

2 Answers

Numbered procs have been in ASE (early 90's: Sybase SQL Server) since at least version 4.2.

And yes, numbered procs are still in ASE today (eg, ASE 16.x).

I never found a use for them and can't recall every coming across their usage (I've worked at a few dozen companies over the years).

I've no idea why these dino's are still around except possibly for backwards compatibility for anyone (still) using them.

As for killing off these dino's ... *shrug* ... I can think of a lot of places (in ASE) where the effort would be better spent. In the meantime, while I know numbered procs exist, I just ignore them and continue on my merry way ...

Answered by markp-fuso on November 16, 2021

Looking at the SQL Server 2000 T-SQL help, there is no mention of deprecation so it seems numbered stored procedures were deprecated with the release SQL Server 2005.

I don't recall numbered procs ever not being in the Microsoft SQL Server product on Windows so I'm pretty sure the feature stems from it's Sybase roots and is in the 4.21a Windows release. I can't speak to the OS/2 version since I dodged that bullet :-)

Answered by Dan Guzman on November 16, 2021

Add your own answers!

Ask a Question

Get help from others!

© 2024 TransWikia.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP