Running different choices within WHERE clause

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 2005 Forums

Transact-SQL (2005)

Running different choices within WHERE clause

Author

Topic

ranalk
Starting Member

49 Posts

Posted - 2010-05-09 : 08:45:54

Hi,

I have the following code that needs to be "dynamic".
on the last AND I want to run different blocks relying on the ACT that was chosen.
Let's say ACT=Run then the block will be AND MsgText like '%Running%'.
if the ACT=Stop then the block will be AND MsgText like '%stopped%'.

DECLARE @Room VARCHAR(4),@Act VARCHAR(10),@Date DATETIME

SET @Room=''
SET @Act=''
SET @Date=''

SELECT *
FROM tempo WITH(nolock)
WHERE
MsgDate=@Date
AND MsgText LIKE '%'+@Room+'%'
AND

Thanks!

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-05-09 : 10:24:49

Your best bet here is dynamic SQL. http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/

Do note the you will have no index usage due to the leading wildcards. Since you're almost certain to have table scans, that nolock may cause you erratic and incorrect results.

--
Gail Shaw
SQL Server MVP

malpashaa
Constraint Violating Yak Guru

264 Posts

Posted - 2010-05-09 : 12:26:16

You can use CASE like this:


AND MsgText LIKE '%' + CASE WHEN Act = 'Run' THEN 'Running'
                          WHEN Act = 'Stop' THEN 'stopped'
                          ELSE ''
                     END + '%'

ms65g
Constraint Violating Yak Guru

497 Posts

Posted - 2010-05-09 : 16:13:08

Or worse approach is:


AND SIGN((CASE WHEN Act = 'Run' AND MsgText LIKE '%Running%' THEN 1 ELSE 0)
           +(CASE WHEN Act = 'Stop' AND MsgText LIKE '%Stopped%' THEN 1 ELSE 0)) = 1

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources