SA0190 : Numbered stored procedures are deprecated

The problem being addressed involves the use of numbered stored procedures in T-SQL code. This feature is deprecated and will be removed in future SQL Server releases, posing a risk for applications relying on this functionality.

For example:

1-- Example of numbered stored procedure
2CREATE PROCEDURE ExampleProcedure;1
3AS
4BEGIN
5    SELECT * FROM SomeTable;
6END;

Using numbered stored procedures, as shown above, is problematic because they limit flexibility and compatibility with future SQL Server versions. Microsoft has flagged this feature for removal, meaning any reliance on it now could result in unsupported code down the line.

• Code maintenance becomes more difficult as updates to SQL Server may deprecate this feature, leading to unexpected failures.

• Performance optimization and database refactoring become more complex without the support for numbered stored procedures.

Address the use of numbered stored procedures, which are deprecated and will be removed in future SQL Server versions, to ensure compatibility and maintainability.

Follow these steps to address the issue:

1. Identify all numbered stored procedures in your database. You can query the system views to locate these procedures. Execute the following statement to find them:

2. Once identified, proceed to rename and renumber these procedures to conventional stored procedures. Use ALTER PROCEDURE or recreate them with CREATE PROCEDURE without numbering. For example, convert CREATE PROCEDURE ExampleProcedure;1 to CREATE PROCEDURE ExampleProcedure.

3. Update any application code or SQL scripts that reference the old numbered procedures so they call the new procedure names. This ensures continuity and prevents errors.

4. Thoroughly test your applications and database logic to verify that removing the numbers has not introduced any errors.

For example:

 1-- Before: Numbered stored procedure
 2CREATE PROCEDURE ExampleProcedure;1
 3AS
 4BEGIN
 5    SELECT * FROM SomeTable;
 6END;
 7
 8-- After: Standard stored procedure
 9CREATE PROCEDURE ExampleProcedure
10AS
11BEGIN
12    SELECT * FROM SomeTable;
13END;

The rule has a Batch scope and is applied only on the SQL script.

Rule has no parameters.

The rule does not need Analysis Context or SQL Connection.

Design Rules, Deprecated Features, Bugs

There is no additional info for this rule.

Other Resources