JD
Lv 4

SQL Server 2005 - Evaluating expressions that are case sensitive - How to do without changing DB collation?

Let's say I want to compare two expressions in a stored procedure. - something like this....

Declare @stored_password nvarchar(20);

Declare @user_input_password nvarchar(20);

-- Note: Stored password is actually SELECTED from a table -

SET stored_password = 'PassWord'

--Note: this variable is set by user input --

SET user_input_password = 'password'

IF stored_password = user_input_password

BEGIN

--passwords match

END

ELSE

BEGIN

--passwords don't match

END

Currently, with a case Insensitive collation, those two expressions evaluate to TRUE. How do I evaluate case sensitive expressions without changing the server collation? There are very fer evaluations that require this for this application.

Thanks.

Update:

I appologize --- I forgot the @ signs in front of my Variables..... assume the syntax is correct - Thanks!

Update 2:

Kick A** man! So the key is to convert the expressions to Binary.

Thanks! That was a fast answer too!

Update 3:

Works perfectly!

Update 4:

You didn't have to change your answer - the first answer worked fine.... I pasted the Query into MS SQL Management Studio.

Update 5:

-- oh right, you just compacted it. Thanks again.

2 Answers

Relevance
  • 1 decade ago
    Favorite Answer

    Try this:

    IF CONVERT(VARBINARY(40), @stored_password) = CONVERT(VARBINARY(40), @user_input_password)

    BEGIN

    --passwords match

    END

    ELSE

    BEGIN

    --passwords don't match

    END

  • 4 years ago

    on account that sq. 2008 has been out for a on an identical time as i could stay faraway from 2000 and concentration on 2005 and 2008. do no longer anticipate any keep could have a single version of the DB around. we are at the instant deploying 2008 for new systems we setup and cutting-edge 2005 systems would be migrated over the subsequent twelve months. i does not waste any time on 2000 except there's a particular activity you're going for that makes use of it.

Still have questions? Get your answers by asking now.