Help with the WHERE clause - SQL Server Forums

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

Transact-SQL (2000)

Help with the WHERE clause

Author

Topic

ibryan
Starting Member

3 Posts

Posted - 2004-09-01 : 21:17:40

I'm pretty new to SQL Server and I'm trying to move one of my SQL statements from ASP into a stored procedure.
Problem is I'm not sure how to create a CASE statement inside my WHERE clause, or if it can be done. Here is my ASP code:

strSQL = "SELECT "
strSQL = strSQL & "name, "
strSQL = strSQL & "dept, "
strSQL = strSQL & "gender, "
strSQL = strSQL & "center "
strSQL = strSQL & "FROM "
strSQL = strSQL & "employees "
if blCriteria then
strSQL = strSQL & "WHERE "
if lvName <> "" then
if lvrbmatch = "exact" then
strSQL = strSQL & optjoin & " LOWER(name) = '" & Replace(lvName,"'","''") & "' "
else if lvrbmatch = "beginning" then
strSQL = strSQL & optjoin & " LOWER(name) LIKE '" & Replace(lvName,"'","''") & "%' "
else if lvrbmatch = "contains" then
strSQL = strSQL & optjoin & " LOWER(name) LIKE '%" & Replace(lvName,"'","''") & "%' "
else
strSQL = strSQL & optjoin & " LOWER(name) LIKE '%" & Replace(lvName,"'","''") & "' "
end if
end if
end if
optjoin = "and"
end if
if lvDept <> "any" then
strSQL = strSQL & optjoin & " dept like '%" & lvDept & "%' "
optjoin = "and"
end if
if lvCenter <> "" then
strSQL = strSQL & optjoin & " center like '%" & lvCenter & "%' "
optjoin = "and"
end if
if lvrbgender <> "both" then
strSQL = strSQL & optjoin & " gender like '%" & lvrbgender & "%' "
optjoin = "and"
end if
end if
strSQL = strSQL & "ORDER BY name "

I'm wondering if I can do something like this in the sp:

WHERE
CASE
WHEN @lvrbmatch = 'exact' THEN LOWER(name) = @name
WHEN @lvrbmatch = 'beginning' THEN LOWER(name) LIKE @name + '%'
WHEN @lvrbmatch = 'contains' THEN LOWER(name) LIKE '%' + @name + '%'
ELSE LOWER(name) LIKE '%" + @name
END AND
CASE
WHEN @lvDept = <> 'any' THEN dept LIKE '%' + @lvDept
WHEN @lvCenter <> '' THEN center LIKE '%' + @lvCenter
WHEN @lvrbgender <> 'both' THEN gender LIKE '%' @lvrbgender
END AND
...

I'd appreciate any help on how to do this.

Thanks
Ian

mk_garg20
Constraint Violating Yak Guru

343 Posts

Posted - 2004-09-01 : 21:24:43

I think you can.
Have you tried executing it?

mk_garg

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-09-01 : 21:42:54

Try:

...
WHERE
(lower(name) like ((CASE WHEN @lvrbmatch in ('ending','contains') THEN '%' else '' end) + @name + (CASE WHEN @lvrbmatch in ('beginning','contains') THEN '%' else '' end))
And
...

Corey

ibryan
Starting Member

3 Posts

Posted - 2004-09-01 : 22:34:50

Thanks for the quick reply..

I tried:
WHERE
LOWER(name) LIKE ((CASE WHEN @lvrbmatch in ('ending','contains') THEN '%' else '' end) + @name + (CASE WHEN @lvrbmatch in ('beginning','contains') THEN '%' else '' end))

and it seems to work. I tried adding another CASE (below) so if @lvrbmatch is equal to 'exact' it should be "=" not "LIKE" and messed it up. Any Ideas?

LOWER(name) (CASE WHEN @lvrbmatch = ('exact') THEN '=' else 'LIKE' end) ((CASE WHEN @lvrbmatch in ('ending','contains') THEN '%' else '' end) + @name + (CASE WHEN @lvrbmatch in ('beginning','contains') THEN '%' else '' end))

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-09-02 : 00:05:40

= 'someword'
is pretty much the same as
like 'someword'

So for 'exact' you could just leave off the wildcards...

Corey

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources