2006/09/04

Numbered stored procedures will be deprecated

I was just browsing the BOL regarding CREATE PROCEDURE and found some words about numbered stored procedures:

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 have some of them in my corporate application. The purpose is to make somewhat like (or behaves like) overloaded functions and methods in OOP (object oriented programming), i.e:
void foo(int x) {
   printf("x is %d\n", x);
}
void foo(char *x) {
   printf("x is '%s'\n", x);
}

In T-SQL we would have:

CREATE PROCEDURE [dbo].[foo];1 @x int AS
  PRINT 'x is ' + CONVERT(varchar(8), @x)
GO
CREATE PROCEDURE [dbo].[foo];2 @x char AS
  PRINT 'x is ' + @x
GO

The fact is that in order to call those stored procedures you need to know the 'suffix' to be applied, such as:

EXEC foo;1 33
-- x is 33
EXEC foo;2 'a'
-- x is a
EXEC foo;1 'a'
--Msg 8114, Level 16, Status 1, Procedure foo, Line 0
--Error converting data type varchar to int.

Thus makes this feature useless from the point of view of behaving like OOP overloaded functions. For that reason and, since Microsoft has planned to deprecate numbered stored procedures for next releases of SQL Server, I will remove them from my application (and so should you do too). The easiest way to achieve the same functionality with a minimum effort is to make:

CREATE PROCEDURE [dbo].[foo_1] @x int AS
  PRINT 'x is ' + CONVERT(varchar(8), @x)
GO
CREATE PROCEDURE [dbo].[foo_2] @x char AS
  PRINT 'x is ' + @x
GO

If you want to know if your database uses numbered stored procedures somewhere you can execute this query:

SELECT DISTINCT sys.objects.name, sys.sql_modules.definition
FROM sys.sql_modules
  INNER JOIN sys.objects on sys.sql_modules.object_id = sys.objects.object_id
WHERE sys.sql_modules.definition LIKE '%;1%'
   OR sys.sql_modules.definition LIKE '%;2%'
   OR sys.sql_modules.definition LIKE '%;3%'

No comments: