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