SA0052 : Avoid using undocumented and deprecated stored procedures

Using deprecated or undocumented stored procedures in T-SQL code can lead to future compatibility issues.

Description

In Microsoft SQL Server, using deprecated stored procedures or those that are undocumented may lead to significant problems as these procedures can be removed or altered in future SQL Server updates or releases. This can result in failures or unexpected behavior in your database applications.

For example:

SQL
Copy
1-- Example of using a deprecated stored procedure
2EXEC sp_addtype 'MyType', 'VARCHAR(100)';

In this example, sp_addtype is a deprecated stored procedure that could be removed in a future version of SQL Server. Such reliance may cause your scripts to break if the procedure is no longer available or its behavior changes.

  • Future SQL Server updates may remove deprecated stored procedures, breaking existing scripts.

  • Undocumented procedures are not guaranteed to maintain consistent behavior, leading to potential application inconsistencies.

How to fix

This section provides guidance on how to address the use of deprecated or undocumented stored procedures in T-SQL code to maintain future compatibility with SQL Server updates.

Follow these steps to address the issue:

  1. Identify and list all instances of deprecated or undocumented stored procedures in your existing T-SQL code. You can use tools like SQL Enlight or static code analysis features within SQL Server Management Studio (SSMS) to assist in this step.

  2. Review the official Microsoft documentation to find recommended alternatives to the deprecated stored procedures. For example, for the deprecated sp_addtype, consider using CREATE TYPE.

  3. Update your codebase to replace each deprecated or undocumented procedure with a supported alternative. Ensure that the new implementation maintains the existing functionality.

  4. Thoroughly test the updated code to verify the changes do not introduce bugs or alter desired behavior. This involves running scripts and applications that depend on the modified code.

  5. Plan for future maintenance by staying up to date with SQL Server documentation regarding deprecation and changes in subsequent versions.

For example:

SQL
Copy
1-- Example of corrected query replacing deprecated procedure
2CREATE TYPE MyType FROM VARCHAR(100);

Scope

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

Parameters

Rule has no parameters.

Remarks

The rule does not need Analysis Context or SQL Connection.

Effort To Fix
1 hour per issue.
Categories

Design Rules, Deprecated Features, Bugs

Additional Information

There is no additional info for this rule.

Example Test SQL
SQL
Copy
 1DECLARE  @proc_name AS VARCHAR( 30 ) = 'sys.sp_addlogin';
 2
 3-- sys.sp_addlogin procedure is deprecated.
 4EXEC @proc_name  'Victoria'
 5               , 'B1r12-36';
 6
 7EXEC sp_addlogin  'Victoria'
 8                , 'B1r12-36';
 9
10-- sys.sp_droplogin procedure is deprecated.
11EXECUTE sys.sp_droplogin  'Michael'
12
13-- sys.sp_bindrule procedure is deprecated.
14EXEC sp_bindrule  'today'
15                , 'HumanResources.Employee.HireDate'
16
17EXEC sys.sp_dropalias  'reportUser'
Analysis Results
  Message Line Column
1 SA0052 : Avoid using stored procedure [sp_addlogin] as it is either deprecated or undocumented. 7 5
2 SA0052 : Avoid using stored procedure [sp_droplogin] as it is either deprecated or undocumented. 11 12
3 SA0052 : Avoid using stored procedure [sp_bindrule] as it is either deprecated or undocumented. 14 5
4 SA0052 : Avoid using stored procedure [sp_dropalias] as it is either deprecated or undocumented. 17 9
See Also

Other Resources

Analysis Rules