Making a Stored Procedure Case Sensitive

How can I make my stored procedure recognize case in data that it processes? Now, it assumes that A is the same as a.

You can successfully make your stored procedure case sensitive in SQL Server 2000 and 7.0, although you use different methods in each version.

In SQL Server 7.0. Let's say you have character-based data stored in a table column or provided to the stored procedure as an argument. If you convert that data to a binary data type, the stored procedure can distinguish between upper and lower case. For example, using the Authors table in the Pubs database, the following query is supposed to retrieve all employee details for employees with the last name green. However, in a case-insensitive environment, the following query would return employees with last names Green, GrEeN, and so on:

SELECT *FROM AuthorsWHERE au_lname = 'green'

In contrast, the following query would return only employees whose last name green is in lower case:

SELECT *FROM AuthorsWHERE CAST(au_lname AS varbinary(40)) = CAST('green' AS varbinary(40))

Note that the query optimizer won't consider using an index on the au_lname column for the above query because the au_lname column isn't specified alone but as part of a function.

Here's a trick I learned from SQL Server MVP Umachandar Jayachandran. If you want the query optimizer to consider using an index on the au_lname column for good query performance, add the original filter to the WHERE clause:

SELECT *FROM AuthorsWHERE CAST(au_lname AS varbinary(40)) = CAST('green' AS varbinary(40))   AND au_lname = 'green'

In SQL Server 2000. Using the COLLATE clause to convert your data to a case-sensitive collation is the most efficient way to retrieve the data you want. When you incorporate COLLATE in your query, you can add the original filter to the WHERE clause so that the query optimizer will consider using an index:

SELECT *FROM AuthorsWHERE au_lname COLLATE Latin1_General_CS_AS = 'green'    AND au_lname = 'green'