SA0190 : Numbered stored procedures are deprecated
Introduction
Section titled “Introduction”Creating numbered stored procedures can lead to compatibility issues in future SQL Server versions.
Description
Section titled “Description”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:
-- Example of numbered stored procedureCREATE PROCEDURE ExampleProcedure;1ASBEGIN SELECT * FROM SomeTable;END;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.
How to fix
Section titled “How to fix”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:
-- Before: Numbered stored procedureCREATE PROCEDURE ExampleProcedure;1ASBEGIN SELECT * FROM SomeTable;END;
-- After: Standard stored procedureCREATE PROCEDURE ExampleProcedureASBEGIN SELECT * FROM SomeTable;END;The rule has a Batch scope and is applied only on the SQL script.
Parameters
Section titled “Parameters”Rule has no parameters.
Remarks
Section titled “Remarks”The rule does not need Analysis Context or SQL Connection.
Effort To Fix
Section titled “Effort To Fix”1 hour per issue.
Categories
Section titled “Categories”Design Rules, Deprecated Features, Bugs
Additional Information
Section titled “Additional Information”There is no additional info for this rule.
Example Test SQL
Section titled “Example Test SQL”CREATE PROCEDURE HumanResources.uspGetAllEmployees ;1AS SET NOCOUNT ON; SELECT LastName, FirstName, JobTitle, Department FROM HumanResources.vEmployeeDepartment;Analysis Results
Section titled “Analysis Results”| Message | Line | Column | |
|---|---|---|---|
| 1 | SA0190 : Numbered stored procedures are deprecated. | 1 | 51 |