Skip to content

SA0190 : Numbered stored procedures are deprecated

Creating numbered stored procedures can lead to compatibility issues in future SQL Server versions.

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 procedure
CREATE PROCEDURE ExampleProcedure;1
AS
BEGIN
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.

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 procedure
CREATE PROCEDURE ExampleProcedure;1
AS
BEGIN
SELECT * FROM SomeTable;
END;
-- After: Standard stored procedure
CREATE PROCEDURE ExampleProcedure
AS
BEGIN
SELECT * FROM SomeTable;
END;

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.

1 hour per issue.

Design Rules, Deprecated Features, Bugs

There is no additional info for this rule.

CREATE PROCEDURE HumanResources.uspGetAllEmployees ;1
AS
SET NOCOUNT ON;
SELECT LastName, FirstName, JobTitle, Department
FROM HumanResources.vEmployeeDepartment;
  Message Line Column
1 SA0190 : Numbered stored procedures are deprecated. 1 51

Analysis Rules