SA0064B : Check stored procedure names used in CREATE PROCEDURE statements for following specified naming convention

Ensure stored procedure names follow a consistent naming convention to improve database management and clarity.

Description

In SQL Server, having consistent naming conventions for stored procedures is crucial for maintaining an organized and manageable database schema. It helps developers and administrators quickly understand the purpose and scope of stored procedures, leading to better maintainability and collaboration.

For example:

SQL
Copy
1-- Example of inconsistent naming convention
2CREATE PROCEDURE uspGetUserData
3AS
4BEGIN
5    SELECT * FROM Users;
6END;

The above example uses a naming prefix that may not align with the team’s agreed standards, leading to confusion and potential errors when integrating with other procedures or applications.

  • Inconsistent naming can lead to errors during development and integration tasks.

  • It makes understanding the database structure more difficult for new team members.

  • Non-standard names can lead to difficulties in automated processes or scripts that rely on specific naming patterns.

`

How to fix

Ensure that stored procedure names adhere to a consistent naming convention to enhance database management and maintainability.

Follow these steps to address the issue:

  1. Identify the stored procedures with non-standard names by reviewing your database schema. This can be done manually or using a script to list stored procedures.

  2. Determine the agreed-upon naming convention for stored procedures in your organization. This often involves a prefix indicating the procedure type (e.g., sp_ for stored procedures, usp_ for user-defined stored procedures).

  3. Rename the stored procedures to align with the naming convention. Use the sp_rename function in SQL Server to rename procedures, ensuring minimal disruption:

  4. Update any scripts or code dependencies that reference the old stored procedure names to reflect the new naming conventions.

For example:

SQL
Copy
1-- Rename a stored procedure to fit the naming convention
2EXEC sp_rename 'uspGetUserData', 'sp_GetUserData';

Scope

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

Parameters
Name Description Default Value
NamePattern

Stored procedure name pattern.

regexp:usp[A-Z][A-Za-z]+
SchemaQualifiedNamePattern

Schema qualified name pattern.

–
Remarks

The rule does not need Analysis Context or SQL Connection.

Effort To Fix
8 minutes per issue.
Categories

Naming Rules, Code Smells

Additional Information

There is no additional info for this rule.

Example Test SQL
SQL
Copy
1CREATE PROCEDURE HumanResources.usp_GetAllEmployees
2AS
3    SELECT LastName, FirstName, JobTitle, Department
4    FROM HumanResources.vEmployeeDepartment;
Analysis Results
  Message Line Column
1 SA0064B : The procedure [HumanResources].[usp_GetAllEmployees] does not match the naming convention. The expected key name is [usp[A-Z][A-Za-z]+]. 1 32
See Also

Other Resources

Analysis Rules