SQL SERVER – Case Sensitive SQL query search using Collate

SQL SERVER is not case sensitive therefore there are scenarios in which SQL query search may provide same result for various inputs which only differ with respect to capital and small letter. In such cases Collate clause should be used.

For example :-

Consider tblLogin Table with following structure :-

The above table contain following information :-

Create a stored procedure to get UserName based on passed UserEmail and Password

Alter PROCEDURE sp_CheckUserLogin
      -- Adding parameters for the stored procedure
      @Email varchar(50),
      @Pwd varchar(50)
AS
BEGIN
     
    -- Select User Details from tblLogin Table
      select UserName from tblLogin where [UserEmail] = @Email and [Password] = @Pwd
     
END

Execute above stored procedure with following statements:

exec sp_CheckUserLoginCredential 'suraj@gmail.com','SuraJ@123'

exec sp_CheckUserLoginCredential 'suraj@gmail.com','Suraj@123'

In above statements output will remain same,it does not matter password passed is in capital or small letter or any combination of small and capital letter.

Output :

This should not happen,to avoid this collate clause should be used :

Alter the procedure sp_CheckUserLogin by adding Collate clause

Alter PROCEDURE sp_CheckUserLogin
      --  Adding parameters for the stored procedure
      @Email varchar(50),
      @Pwd varchar(50)
AS
BEGIN

    -- Select User Details from tblLogin Table
      select UserName from tblLogin where [UserEmail] = @Email and [Password] = @Pwd COLLATE Latin1_General_CS_AS
     
END

Now executing stored procedure will only give result for case sensitive password.