Make SQL Server distinguish between uppercase and lowercase characters in a stored procedu

I would like SQL Server 2000 to distinguish between uppercase and lowercase letters, but only within a single stored procedure. Also, at the end of the sp, I want the original collation to be restored. How will I implement this in my sp?

Use the COLLATE command and change
WHERE A = B
to
WHERE A COLLATE SQL_Latin1_General_Cp850_CS_AS = B COLLATE SQL_Latin1_General_Cp850_CS_AS

This assumes that SQL_Latin1_General_Cp850_CI_AS is your normal collation. The change in collation is effectivle only for the scope of the one clause.

|||

I adopted a different approach.

I converted using the convert function to varbinary(50). My original column was varchar(50)

For example if 'column1' value was being compared with@.column1 parameter, then the following solution helped me in achieving my goal:

CONVERT(varbinary(50), column1) = CONVERT(varbinary(50), @.column1)

My only question is if I have a 50 character varchar then when I convert it to varbinary, should I convert to varbinary(50)?

The advantage of this is that I don't need to disturb the existing collation setting on SQL Server 2000 instance.

|||

I ran
DECLARE @.VAR VARCHAR(50)
SET @.VAR = '12345'
PRINT DATALENGTH(@.VAR)
PRINT DATALENGTH(CONVERT(VARBINARY(50), @.VAR))

and got
5
5

which shows that the lengths can be the same.

>The advantage of this is that I don't need to disturb the existing collation setting on SQL Server 2000 instance.
The COLLATE command used as I describe does not change the collation sequence of the database, merely the sequence the comparison is done in.