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

The topic describes the SA0231 analysis rule.

Message

The used parameter or variable has different case than its declaration

Description

The rule checks T-SQL code for parameters and variables referenced using case different from their declaration.

Using inconsistent variable case will break the code if deployed on case sensitive server instance.

How to fix

Ensure that the reported variable or parameter has the same case as its declaration.

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.
Categories

Naming Rules, Code Smells

Additional Information

There is no additional info for this rule.

Example Test SQL
SQL
 1create procedure SampleProcedure
 2@EmployeeIDParm int,
 3@EmployeeFirstnameParm nvarchar(50),
 4@EmployeeLastnameParm nvarchar(50),
 5@maxTotal int output
 6as 
 7begin
 8    declare @myCounter int;
 9    declare @lastName nvarchar(30),
10            @firstName nvarchar(20),
11            @StateProvince nchar(2);
12
13    execute @MyCounter=sp_executesql 'select 1,2,3',
14            @params='',
15            @EmployeeIDParm=1,
16            @EmployeelastnameParm=@EmployeelastnameParm,
17            @EMPLOYEEIDPARM,
18            @maxTotal=@maxTotal output;
19
20    select @maxtotal,
21           @EmployeeLastNameParm,
22           @stateProvince,
23           @firstName,
24           @lastname;
25
26    return 1
27end

Example Test SQL with Automatic Fix
SQL
 1create procedure SampleProcedure
 2@EmployeeIDParm int,
 3@EmployeeFirstnameParm nvarchar(50),
 4@EmployeeLastnameParm nvarchar(50),
 5@maxTotal int output
 6as 
 7begin
 8    declare @myCounter int;
 9    declare @lastName nvarchar(30),
10            @firstName nvarchar(20),
11            @StateProvince nchar(2);
12
13    execute @myCounter=sp_executesql 'select 1,2,3',
14            @params='',
15            @EmployeeIDParm=1,
16            @EmployeelastnameParm=@EmployeeLastnameParm,
17            @EmployeeIDParm,
18            @maxTotal=@maxTotal output;
19
20    select @maxTotal,
21           @EmployeeLastnameParm,
22           @StateProvince,
23           @firstName,
24           @lastName;
25
26    return 1
27end

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
See Also

Other Resources