SQL Stored Proc, case sensitive?

Last Post 11 Dec 2008 05:29 AM by dstoltz. 2 Replies.
AddThis - Bookmarking and Sharing Button
Author Messages
dstoltz
New Member
New Member

--
11 Dec 2008 05:18 AM
Folks, below is my stored procedure...and because the password is encrypted, it's already case-sensitive...I'm wondering how in this SP I can make the username case-sensitive?

CREATE PROCEDURE login
(
@username varchar(15),
@password varchar(1000)
)
AS
SELECT username FROM users WHERE username=@username AND password=@password

GO


Thanks for any direction....
dstoltz
New Member
New Member

--
11 Dec 2008 05:29 AM
Never mind - I used the cast as follows, which seems to work:

CREATE PROCEDURE login
(
@username varchar(15),
@password varchar(1000)
)
AS
SELECT username FROM users WHERE cast(username as varbinary(15))=cast(@username as varbinary(15)) AND password=@password
GO
nosepicker
New Member
New Member

--
11 Dec 2008 10:38 AM
Probably a cleaner way to do it is to use collations to temporarily change the case sensitivity. For example, if your collation is the default of SQL_Latin1_General_Cp1_CI_AS (case insensitive, as indicated by the "CI"), then you can change to SQL_Latin1_General_Cp1_CS_AS (case sensitive, as indicated by the "CS") as such:

CREATE PROCEDURE login
(
@username varchar(15),
@password varchar(1000)
)
AS
SELECT username FROM users
WHERE username COLLATE SQL_Latin1_General_Cp1_CS_AS = @username COLLATE SQL_Latin1_General_Cp1_CS_AS
AND password COLLATE SQL_Latin1_General_Cp1_CS_AS = @password COLLATE SQL_Latin1_General_Cp1_CS_AS
GO


Acceptable Use Policy
---