Define a case sensitive column

• Elad Levi

  Newbie

  Points: 7

  More actions

  September 2, 2006 at 10:32 am

  #170015

  I have "Users" table with "UserName and "Password" columns.

  NOTE: I wrote this stored procedure

  ALTER PROCEDURE

  dbo.sp_User_Select

  (

  @UserName

  nchar(15),

  @Password

  nchar(10)

  )

  AS

  Select *

  From Users

  Where UserName = @UserName AND Password = @Password

  RETURN

  I want to set these two columns to case sensitive how can i do it?

  Thanks In Advance

• Ninja's_RGR'us

  SSC Guru

  Points: 294069

  More actions

  September 2, 2006 at 4:26 pm

  #658182

  Well first of all, why don't you use (n)varchar in this case (design and procedure)?

  And your answer with only the password case sensitive :

  /* no need to return the username and pass as you already have that in the application*/

  Select 1 as IsValid from dbo.Users where Username = @UserName and Password COLLATE Latin1_General_CS_AS = @Password

  you can add this criteria if your index (username, password) is not used in the query plan : and Password = @Password

  It might happen because the collate clause makes the search non sargable... and I have no server here to test it and confirm :-(.