SA0231 : The used parameter or variable has different case than its declaration

Introduction

Inconsistent casing for parameters and variables in T-SQL code can lead to errors when deployed on case-sensitive SQL Server instances.

Description

Case sensitivity in SQL Server can lead to issues where parameters and variables declared in one case are referenced in another. This becomes particularly problematic on case-sensitive SQL Server collations, where such mismatches can cause runtime errors.

For example:

-- Problematic query with inconsistent case usage
DECLARE @ExampleVar INT;
SET @examplevar = 10;

In this example, the variable @ExampleVar is declared with a capital ‘E’ and ‘V’, but is referenced with lowercase ‘e’ and ‘v’. On a case-sensitive server, SQL Server treats @ExampleVar and @examplevar as different entities, leading to errors.

Code errors in production environments due to case sensitivity not caught during development.
Potential difficulties in debugging and maintaining code due to inconsistent naming conventions.

How to fix

Ensure consistent usage of letter casing for variables and parameters in T-SQL code to prevent runtime errors on case-sensitive SQL Server instances.

Follow these steps to address the issue:

1.Identify all instances where the variable or parameter is declared using DECLARE statements within your T-SQL code.

2.Ensure that all references to each variable or parameter throughout your T-SQL code match the exact case used in the declaration. For example, if a variable is declared as @ExampleVar , all references should use @ExampleVar .

3.Review your code for any discrepancies in case, particularly in assignments and usage within queries and control structures.

For example:

-- Corrected query with consistent case usage
DECLARE @ExampleVar INT;
SET @ExampleVar = 10;

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

2 minutes per issue.

Additional Information

There is no additional info for this rule.

Example Test SQL

create procedure SampleProcedure
@EmployeeIDParm int,
@EmployeeFirstnameParm nvarchar(50),
@EmployeeLastnameParm nvarchar(50),
@maxTotal int output
as
begin
    declare @myCounter int;
    declare @lastName nvarchar(30),
            @firstName nvarchar(20),
            @StateProvince nchar(2);

    execute @MyCounter=sp_executesql 'select 1,2,3',
            @params='',
            @EmployeeIDParm=1,
            @EmployeelastnameParm=@EmployeelastnameParm,
            @EMPLOYEEIDPARM,
            @maxTotal=@maxTotal output;

    select @maxtotal,
           @EmployeeLastNameParm,
           @stateProvince,
           @firstName,
           @lastname;

    return 1
end

Example Test SQL with Automatic Fix

create procedure SampleProcedure
@EmployeeIDParm int,
@EmployeeFirstnameParm nvarchar(50),
@EmployeeLastnameParm nvarchar(50),
@maxTotal int output
as
begin
    declare @myCounter int;
    declare @lastName nvarchar(30),
            @firstName nvarchar(20),
            @StateProvince nchar(2);

    execute @myCounter=sp_executesql 'select 1,2,3',
            @params='',
            @EmployeeIDParm=1,
            @EmployeelastnameParm=@EmployeeLastnameParm,
            @EmployeeIDParm,
            @maxTotal=@maxTotal output;

    select @maxTotal,
           @EmployeeLastnameParm,
           @StateProvince,
           @firstName,
           @lastName;

    return 1
end

Analysis Results

	Message	Line	Column
1	SA0231 : The reference @EMPLOYEEIDPARM is used with different case than the declared parameter @EmployeeIDParm.	17	12
2	SA0231 : The reference @EmployeelastnameParm is used with different case than the declared parameter @EmployeeLastnameParm.	16	34
3	SA0231 : The reference @EmployeeLastNameParm is used with different case than the declared parameter @EmployeeLastnameParm.	21	11
4	SA0231 : The reference @maxtotal is used with different case than the declared parameter @maxTotal.	20	11
5	SA0231 : The reference @MyCounter is used with different case than the declared variable @myCounter.	13	12
6	SA0231 : The reference @lastname is used with different case than the declared variable @lastName.	24	11
7	SA0231 : The reference @stateProvince is used with different case than the declared variable @StateProvince.	22	11

Analysis Rules