Case sensitive in stored procedures

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Case sensitive in stored procedures

Author

Topic

Codesensitive
Starting Member

11 Posts

Posted - 2002-05-02 : 22:17:37

G'day,
I use a stored procedure for authorizing users in an application. The problem is that the stored procedure doesn't seem to be case sensitve.
How do I make it case sensitive?

CREATE PROCEDURE proc_authority_login
@User varchar(50),
@Password varchar(50)
AS
set nocount on
if exists(select * from users_20 where s_name_20 = @User and s_password_20 = @Password)
print 'Success'
else
print 'Failed'
GO

SKIBUM
Starting Member

32 Posts

Posted - 2002-05-03 : 00:27:09

All you need to do is CONVERT to VarBinary when doing the comparisons....

if exists(select * from users_20 where CONVERT(varbinary,s_name_20) = CONVERT(varbinary,@User) and CONVERT(varbinary,s_password_20) = CONVERT(varbinary,@Password))

This will give you a case-sensitive effect.

Good luck!

khalik
Constraint Violating Yak Guru

443 Posts

Posted - 2002-05-03 : 04:52:02

when installing the sql server u can enfore that the case sensitive or not.... AFAIK that can be set there... any one knows about the chaning the parameter

======================================
Ask to your self before u ask someone

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2002-05-03 : 07:01:23

case sensitivity is a feature of COLLATION...ie it affects the sort order appears in.....

This has come up here before....I think the database (or server) needs to be re-installed.....searching using the COLLATION keyword will bring up appropriate topics.

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2002-05-03 : 07:02:17

That will change the case sensitivity for everything. That will mean "Users" will not be the same as "users". Big pain.

I would go with the binary comparison option.

Damian

empire
Starting Member

40 Posts

Posted - 2002-05-03 : 11:04:27

I use case sensitivity on my servers, I like it, yes ‘Users’ != ‘users’, but then again it forces my coders to pay attention to object names so I don’t end up reading code like

exec spRptMyReport
exec sprptmyReport
exec sprptmyreport
exec SPRPTMYREPORT

Its the first one or no go. It does make you have to pay attention to string comparisons, but I think that’s a good thing as well.

So I would advocate making the Server Case Sensitive.

Programmers are simple devices, put caffeine in, and get code out.

SKIBUM
Starting Member

32 Posts

Posted - 2002-05-03 : 13:47:26

You guys are blowing this out of proportion. All he needs to do is the binary comparison that I demonstrated above.

To give the advice of reinstalling the SQL Server is extreme in this case.

lfmn
Posting Yak Master

141 Posts

Posted - 2002-05-03 : 14:07:25

I thought I was the only one who preferred case sensitive databases. I agree that it makes for more uniform code, but more importantly, it improves performance. According to Microsoft SQL Server 7.0 DBA Survival Guide on page 92 - "Binary sort order is the fastest of the sort orders; the other sort orders are 20 to 35% slower than the binary sort order"

quote:

I use case sensitivity on my servers, I like it, yes ‘Users’ != ‘users’, but then again it forces my coders to pay attention to object names so I don’t end up reading code like

exec spRptMyReport
exec sprptmyReport
exec sprptmyreport
exec SPRPTMYREPORT

Its the first one or no go. It does make you have to pay attention to string comparisons, but I think that’s a good thing as well.

So I would advocate making the Server Case Sensitive.

Programmers are simple devices, put caffeine in, and get code out.

SQL is useful if you don't know cursors :-)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources