Dynamic WHERE?

[berkshirea]

hi guys, can you help me out here please.

i am testing a simple login page.

i have a table something like this:

id firstname surname
1 freddie green
2 John West
3 marvin jackson
4 Adam Smith
5 Stephen Lamb

i want to return results of everything id=4 and greater and that are case sensitive. so far i have written this sql:

select * from myTable where firstname = 'firstname' and surname = 'surname' COLLATE Latin1_General_CS_AS

and this sql above returns everything from id=4 and greater and stricter in that it should only be of the right case.

BUT i want also to include the old ones, id=3 and less, which includes the results from non-case-sensitive login.

i was trying the dynamic where clause using 'case' but am not successful.

i hope i've explained what i want to convey clearly.

any ideas?

 

[Qik3Coder]

1.) It sounds like at some point you started enforcing a case sensitive login name (still unique?)

2.) This is why login checks are usually not case sensitve, but passwords are.


To answer your question

UNION 2 queries, one with ID > X, one with ID <= X.

If you have a lot of logic in addition to the username/password check, then do the union in a subquery and then join based on that.

Select * 
[b]from ([/b]
select 1 from tableA 
where "case sensitive"
UNION ALL
select 1 from tableA
where !"case sensitve"[b]) as logins[/b]
LEFT JOIN tableB on
 [b]logins.[/b]columnA = b.columnA

HTH,
Lodlaiden




You've got questions and source code. We want both!

 

[jcaulder]

I think you can just OR the conditions together to simplify the query and force the table and input parameter to upper case to ignore stored and entered values prior to ID = 4. No need for dynamic SQL...

declare @name varchar(16)
set @name = 'firstname'
select * from myTable where
(firstname = @name COLLATE Latin1_General_CS_AS
AND ID >= 4)
OR
(Upper(firstname) = Upper(@name) AND ID < 4)


Will that work for your needs or did I miss something?

J

 

[jcaulder]

Oops, you wouldn't need the UPPER in the second part of the condition since by default it is case insensitive:

declare @name varchar(16)
set @name = 'firstname'
select * from myTable where
(firstname = @name COLLATE Latin1_General_CS_AS
AND ID >= 4)
OR
(firstname = @name AND ID < 4)

Sorry about that.

 

[berkshirea]

hi jcaulder,

i tried your suggestion but it says "invalid column name 'myVariableName'". any ideas?

the thing is when i put a real name (instead of the variable name) from the database like the one below, it works. any ideas? thanks

declare @name varchar(16)
set @name = 'firstname'
select * from myTable where
(firstname = 'marvin' COLLATE Latin1_General_CS_AS
AND ID >= 4)
OR
(firstname = 'marvin' AND ID < 4)

 

[jcaulder]

I should first ask "where and how are you creating the query to return the login?" I think you probably have a stored procedure that you are passing in parameters for firstname and surname?

If so, whatever your parameter names are should be inserted into the query. If your parameters are:
@firstname
@surname

then the query should be:

select * from myTable where
--the first part is case sensitive for all ID >= 4 
(
firstname COLLATE Latin1_General_CS_AS = @firstname
surname COLLATE Latin1_General_CS_AS = @surname
AND ID >= 4
)
--the second is case insensitive for all ID < 4
OR
(
firstname = @firstname 
AND surname = @surname
AND ID < 4
)

Replace the @firstname and @surname with whatever your real parameter names are in all locations within the query. Also, the collation is on the column so include it on each if you need multiple columns to be case sensitive like above.

Also, it would be much better to have something guaranteed unique like a UserID field that you are using to authenticate someone rather than first+surname since this can and will repeat for a large set of data. If you only have first and surname and you have two people named John Smith, one with ID = 3 and one with ID = 5, it becomes uncertain which account is returned. I'm assuming you have a third field that contains a password and the combination of the three in the query would usually provide uniqueness but it still isn't guaranteed.

HTH,

J