compare two string in SQL Server

Hi,
I am writing a Store Procedure for Login, as following:
@.p_sUsername, @.p_sPassword are parameters
...
SELECT @.BName = B.username, @.BPW = Password FROM BENUTZER as B WHERE
username = @.p_sUsername AND Passwort = @.p_sPassword
...
but the SELECT Statement can not differ Uppercase and Lowercase, that means,
"Martin" = "martin"
Then I check explicitly:
if @.BName != @.p_sUsername
Login = 0
but this comparing works the same.
What can I do?
Thanks
Martinif you SQL Server database is not case sensitive you have to convert to to a
comparable format ,e.g. varbinary or specifiy a CASE Sensitive (CS) Collatio
n
for it:
Select 1 where 'test' = 'TEST'
GO
Select 1 where 'Test' COLLATE SQL_Latin1_General_CP1_CS_AS = 'TEST' COLLATE
SQL_Latin1_General_CP1_CS_AS
GO
Select 1 Where CAST('Test' as varbinary) = CAST('TEST' as varbinary)
Select 1 where 'test' = 'TEST'
GO
Select 1 where 'Test' COLLATE SQL_Latin1_General_CP1_CS_AS = 'TEST' COLLATE
SQL_Latin1_General_CP1_CS_AS
GO
Select 1 Where CAST('Test' as varbinary) = CAST('TEST' as varbinary)
--
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Martin" wrote:

> Hi,
> I am writing a Store Procedure for Login, as following:
> @.p_sUsername, @.p_sPassword are parameters
> ...
> SELECT @.BName = B.username, @.BPW = Password FROM BENUTZER as B WHERE
> username = @.p_sUsername AND Passwort = @.p_sPassword
> ...
> but the SELECT Statement can not differ Uppercase and Lowercase, that mean
s,
> "Martin" = "martin"
> Then I check explicitly:
> if @.BName != @.p_sUsername
> Login = 0
> but this comparing works the same.
> What can I do?
> Thanks
> Martin
>
>|||Case-sensitivity is determined by the column collation. So choose a
case-sensitive collation. For example:
ALTER TABLE benutzer
ALTER COLUMN username VARCHAR(128) COLLATE Latin1_General_CS_AS ;
David Portas
SQL Server MVP
--|||Thanks
Martin
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> schrieb im
Newsbeitrag news:1125571037.540712.199690@.g43g2000cwa.googlegroups.com...
> Case-sensitivity is determined by the column collation. So choose a
> case-sensitive collation. For example:
> ALTER TABLE benutzer
> ALTER COLUMN username VARCHAR(128) COLLATE Latin1_General_CS_AS ;
> --
> David Portas
> SQL Server MVP
> --
>|||thanks
Martin
"Jens Smeyer" <Jens@.[Remove_that][for contacting me]sqlserver2005.de>
schrieb im Newsbeitrag
news:877CE479-F0DF-4CDA-8720-A14908BFB207@.microsoft.com...
> if you SQL Server database is not case sensitive you have to convert to to
a
> comparable format ,e.g. varbinary or specifiy a CASE Sensitive (CS)
Collation
> for it:
> Select 1 where 'test' = 'TEST'
> GO
>
> Select 1 where 'Test' COLLATE SQL_Latin1_General_CP1_CS_AS = 'TEST'
COLLATE
> SQL_Latin1_General_CP1_CS_AS
> GO
> Select 1 Where CAST('Test' as varbinary) = CAST('TEST' as varbinary)
> Select 1 where 'test' = 'TEST'
> GO
>
> Select 1 where 'Test' COLLATE SQL_Latin1_General_CP1_CS_AS = 'TEST'
COLLATE
> SQL_Latin1_General_CP1_CS_AS
> GO
> Select 1 Where CAST('Test' as varbinary) = CAST('TEST' as varbinary)
> --
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
>
> "Martin" wrote:
>
means,|||To add to the other responses, if you force a case-sensitive compare in your
SQL statement, consider also including the normal compare in your WHERE
clause. This will allow SQL Server to efficiently use indexes on those
columns and thereby improve performance.
SELECT
@.BName = B.username,
@.BPW = Password
FROM BENUTZER as B
WHERE
username COLLATE SQL_Latin1_General_CP1_CS_AS = @.p_sUsername AND
username = @.p_sUsername AND
password COLLATE SQL_Latin1_General_CP1_CS_AS = @.p_sPassword AND
Password = @.p_sPassword
Hope this helps.
Dan Guzman
SQL Server MVP
"Martin" <martinsm@.freenet.de> wrote in message
news:Okeml6trFHA.304@.TK2MSFTNGP11.phx.gbl...
> Hi,
> I am writing a Store Procedure for Login, as following:
> @.p_sUsername, @.p_sPassword are parameters
> ...
> SELECT @.BName = B.username, @.BPW = Password FROM BENUTZER as B WHERE
> username = @.p_sUsername AND Passwort = @.p_sPassword
> ...
> but the SELECT Statement can not differ Uppercase and Lowercase, that
> means,
> "Martin" = "martin"
> Then I check explicitly:
> if @.BName != @.p_sUsername
> Login = 0
> but this comparing works the same.
> What can I do?
> Thanks
> Martin
>