Dynamic Clause WHERE - 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 2005 Forums

Transact-SQL (2005)

Dynamic Clause WHERE

Author

Topic

cidmi.dovic
Yak Posting Veteran

53 Posts

Posted - 2007-03-09 : 09:12:00

Hello,

I'm using Stored Procedures for all my operations against a SQL sErver 2000. Now I found a limitation, what about dynamic clausules WHERE?. I mean, I have a table and I want three fields for filtering the information A,B,C then the clauses WHERE can be:

WHERE A=
WHERE B=
WHERE C=
WHERE A=,B=
WHERE A=,C=
...and so on

Using IF sentences is a shit, so Is there any way to construct dynamic clausules on Transact SQL. I've tried:

DECLARE @FilteringData AS VaRCHAR(100)
SET @FilteringData = 'A=''John'''
SELECT FROM Table WHERE @FilteringData

But this doesn't work.

Any idea?

Thank you and happy weekend.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-09 : 09:13:36

WHERE COALESCE(@A, A) = A
AND COALESCE(@B, B) = B
AND COALESCE(@C, C) = C

Peter Larsson
Helsingborg, Sweden

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2007-03-09 : 09:21:29

what's the difference if you do it in reverse? any performance advantages?

WHERE A=COALESCE(@A, A)
AND B=COALESCE(@B, B)
AND C=COALESCE(@C, C)

quote:
Originally posted by Peso

WHERE COALESCE(@A, A) = A
AND COALESCE(@B, B) = B
AND COALESCE(@C, C) = C

Peter Larsson
Helsingborg, Sweden

--------------------
keeping it simple...

cidmi.dovic
Yak Posting Veteran

53 Posts

Posted - 2007-03-09 : 09:21:49

Thanj You Peso, It works fine!

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-03-09 : 11:42:10

quote:
Originally posted by jen

what's the difference if you do it in reverse? any performance advantages?

WHERE A=COALESCE(@A, A)
AND B=COALESCE(@B, B)
AND C=COALESCE(@C, C)

quote:
Originally posted by Peso

WHERE COALESCE(@A, A) = A
AND COALESCE(@B, B) = B
AND COALESCE(@C, C) = C

Peter Larsson
Helsingborg, Sweden

--------------------
keeping it simple...

no difference.

- Jeff
http://weblogs.sqlteam.com/JeffS

lazerath
Constraint Violating Yak Guru

343 Posts

Posted - 2007-03-09 : 18:54:13

Note that this technique is fine for small to mid size tables, but for larger tables you will need to seek alternatives if you are concerned about performance.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources