SA0230 : Identifier uses different case than object’s actual name

The topic describes the SA0230 analysis rule.

Message

Identifier uses different case than object’s actual name

Description

The rule checks T-SQL code for reference of database object or column having different case than its actual name.

Using inconsistent identifier case will break the code if deployed on case sensitive database or server instance.

How to fix

Ensure that the reported object or column identifier has the same case as the name with which it was created in the database.

Scope

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

Parameters

Rule has no parameters.

Remarks

The rule requires SQL Connection. If there is no connection provided, the rule will be skipped during analysis.

Effort To Fix
2 minutes per issue.
Categories

Naming Rules, Code Smells

Additional Information

There is no additional info for this rule.

Example Test SQL
SQL
 1CREATE PROCEDURE [HumanResourceS].[uspUpdateEmployeeHireInfo]
 2@BusinessEntityID [int], 
 3@JobTitle [nvarchar](50), 
 4@HireDate [datetime], 
 5@RateChangeDate [datetime], 
 6@Rate [money], 
 7@PayFrequency [tinyint], 
 8@CurrentFlag [dbo].[Flag] 
 9WITH EXECUTE AS CALLER
10AS
11BEGIN
12        SET NOCOUNT ON;
13        BEGIN TRY
14                BEGIN TRANSACTION;
15                UPDATE [HumanResources].[EmployeE] 
16                SET [JobTitle] = @JobTitle 
17                ,[HireDate] = @HireDate 
18                ,[CurrentFlag] = @CurrentFlag 
19                WHERE [BusinessEntityId] = @BusinessEntityID;
20                INSERT INTO [HumanResources].[EmployeePayHistory] 
21                ([BusinessEntityID]
22                ,[RateChangeDate]
23                ,[Rate]
24                ,[Payfrequency]) 
25                VALUES (@BusinessEntityID, @RateChangeDate, @Rate, @PayFrequency);
26                COMMIT TRANSACTION;
27        END TRY
28        BEGIN CATCH
29                IF @@TRANCOUNT > 0
30                BEGIN
31                ROLLBACK TRANSACTION;
32                END
33                EXECUTE [dbo].[UspLogError];
34        END CATCH;
35END;

Example Test SQL with Automatic Fix
SQL
 1CREATE PROCEDURE [HumanResources].[uspUpdateEmployeeHireInfo]
 2@BusinessEntityID [int], 
 3@JobTitle [nvarchar](50), 
 4@HireDate [datetime], 
 5@RateChangeDate [datetime], 
 6@Rate [money], 
 7@PayFrequency [tinyint], 
 8@CurrentFlag [dbo].[Flag] 
 9WITH EXECUTE AS CALLER
10AS
11BEGIN
12        SET NOCOUNT ON;
13        BEGIN TRY
14                BEGIN TRANSACTION;
15                UPDATE [HumanResources].[Employee] 
16                SET [JobTitle] = @JobTitle 
17                ,[HireDate] = @HireDate 
18                ,[CurrentFlag] = @CurrentFlag 
19                WHERE [BusinessEntityID] = @BusinessEntityID;
20                INSERT INTO [HumanResources].[EmployeePayHistory] 
21                ([BusinessEntityID]
22                ,[RateChangeDate]
23                ,[Rate]
24                ,[PayFrequency]) 
25                VALUES (@BusinessEntityID, @RateChangeDate, @Rate, @PayFrequency);
26                COMMIT TRANSACTION;
27        END TRY
28        BEGIN CATCH
29                IF @@TRANCOUNT > 0
30                BEGIN
31                ROLLBACK TRANSACTION;
32                END
33                EXECUTE [dbo].[uspLogError];
34        END CATCH;
35END;

Analysis Results
  Message Line Column
1 SA0230 : The case of the identifier [HumanResourceS].[uspUpdateEmployeeHireInfo] does not match the real stored procedure name [HumanResources].[uspUpdateEmployeeHireInfo]. 1 17
2 SA0230 : The case of the identifier [HumanResources].[EmployeE] does not match the real table name [HumanResources].[Employee]. 15 9
3 SA0230 : The case of the identifier [BusinessEntityId] does not match the real column name [BusinessEntityID]. 19 8
4 SA0230 : The case of the identifier [Payfrequency] does not match the real column name [PayFrequency]. 24 3
5 SA0230 : The case of the identifier [dbo].[UspLogError] does not match the real stored procedure name [dbo].[uspLogError]. 33 10
See Also

Other Resources