Stored Procedures are More Evil Than You Think

Gokhan Altinoren — Thu, 08 Mar 2007 10:46:34 GMT

I was doing a code review for a client and something like the following was everywhere:

command.CommandText = "EXEC ManageBlogs;2 '" + blogName + "'";

command.ExecuteReader();

Yes, they don't use parametrized queries. They don't even mark the command as a SP but that's the usual bad practice I happen to see everywhere. But what the heck ";2" stands for? I remember reading something like that, let's fire Books Online for Create Procedure statement (T-SQL 2000, which my client use):

CREATE PROC [ EDURE ] [ owner. ] procedure_name [ ; number ]

;number

Is an optional integer used to group procedures of the same name so they can be dropped together with a single DROP PROCEDURE statement. For example, the procedures used with an application called orders may be named orderproc;1, orderproc;2, and so on. The statement DROP PROCEDURE orderproc drops the entire group. If the name contains delimited identifiers, the number should not be included as part of the identifier; use the appropriate delimiter around procedure_name only.

...and here's the 2005 version:

CREATE { PROC | PROCEDURE } [schema_name.] procedure_name [ ; number ]
...

; number

Is an optional integer that is used to group procedures of the same name. These grouped procedures can be dropped together by using one DROP PROCEDURE statement. For example, an application called orders might use procedures named orderproc;1, orderproc;2, and so on. The DROP PROCEDURE orderproc statement drops the whole group. If the name contains delimited identifiers, the number should not be included as part of the identifier; use the appropriate delimiter around only procedure_name.

Numbered stored procedures have the following restrictions:

Cannot use xml or CLR user-defined types as the data types.
Cannot create a plan guide on a numbered stored procedure.

Note:
This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.

I believe this is something from the archaic days when all the development happened on the DB, that's why SP's for in the first place, isn't it? I even bet this is a fossil syntax leftover from the Sybase SQL days. Good to see that this will be dropped, but it should have already been with SQL 2005.

So, what was my client thinking? Since they use SP's extensively, they use this as a method overload. SP name stands for the static class and the number is something like different methods to call. They use 1 for INSERT, 2 for SELECT and 3 for DELETE and so on.

CREATE PROCEDURE ManageBlogs;1

    @Name varchar(50)

AS

    INSERT INTO Blogs (blog_name) VALUES (@Name)

GO

CREATE PROCEDURE ManageBlogs;2

AS

    SELECT * FROM Blogs

GO

CREATE PROCEDURE ManageBlogs;3

    @Id int

AS

    DELETE FROM Blogs WHERE blog_id = @Id

GO

Usage?

Exec ManageBlogs -- Error

Exec ManageBlogs 'MyBlog' -- Executes ;1

Exec ManageBlogs;1 'MyBlog' -- Executes ;1

Exec ManageBlogs;2 -- Executes ;2

Exec [ManageBlogs];2 -- Executes ;2

Exec [ManageBlogs;2] -- Error

Total chaos :) And I was quick to find ;1's that don't insert in some SP's, so they broke their own rules already.

VS 2005 designer picks up only the first one:

Hey, even Management Studio doesn't support numbered SP's:

I believe I have enough evidence to to convince my client that this is pure evil :)

altinoren.com - SQL

Stored Procedures are More Evil Than You Think