Case Sensitive String Comparison

Question

I need to compare two strings in a If-Else block in a stored procedure such as

If(@USERNAME='rajat')
begin
select * from table
end
else
begin
select * from table1
end

but the problem with the above approach is '=' comparison is case insensitive.I need a approach where the comparison is case sensitive

Answer 1

SQL Server has case sensitivity at the server, database, and column level. This is part of the collation properties. So in your example, it's likely that one or more of these settings has been set to case-insensitive.

-- Check server collation
SELECT SERVERPROPERTY('COLLATION')

-- Check database collation.
SELECT DATABASEPROPERTYEX('AdventureWorks', 'Collation') SQLCollation;

-- Check column collation
select table_name, column_name, collation_name
from information_schema.columns
where table_name = @table_name

Something like this SQL might work for you:

If (@USERNAME='rajat' COLLATE Latin1_General_CS_AS)
.....

Answer 2

If(@USERNAME='rajat' COLLATE Latin1_General_CS_AS )
begin
select * from table
end
else
begin
select * from table1
end