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

SQL Server Administration (2005)

Dynamic WHERE clause

Author

Topic

lionofdezert
Aged Yak Warrior

885 Posts

Posted - 2009-08-10 : 03:58:48

What are the best possibilities to write a query with Dynamic WHERE clause, BUT with out using string.

For exm:
DECLARE @condition varchar(100)

SELECT @condition = ' stringcol1 = ''val1'' AND intcol2 = 2'

@condition can contain any number of columns to compare

rajdaksha
Aged Yak Warrior

595 Posts

Posted - 2009-08-10 : 04:07:18

Hi

Refer ...http://www.sqlteam.com/article/implementing-a-dynamic-where-clause

-------------------------
R..
http://code.msdn.microsoft.com/SQLExamples/
http://msdn.microsoft.com/hi-in/library/bb500155(en-us).aspx

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2009-08-10 : 04:55:11

Use COALESCE or CASE

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

lionofdezert
Aged Yak Warrior

885 Posts

Posted - 2009-08-10 : 05:11:17

Thanx guys for quick response...
It means i have to mention all columns in my WHERE clause with COALESCE which are suppose to be included in dynamic where clause.

BUT if condition change like

For exm:
DECLARE @condition varchar(100)

SELECT @condition = ' stringcol1 = ''val1'' AND intcol2 <> 2'

@condition can contain any number of columns to compare

Now first time intcol2 = 2 and second time condition is intcol2<>2
WHAT SHOULD I DO NOW ?

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2009-08-10 : 05:31:32

quote:
Originally posted by lionofdezert

Thanx guys for quick response...
It means i have to mention all columns in my WHERE clause with COALESCE which are suppose to be included in dynamic where clause.

BUT if condition change like

For exm:
DECLARE @condition varchar(100)

SELECT @condition = ' stringcol1 = ''val1'' AND intcol2 <> 2'

@condition can contain any number of columns to compare

Now first time intcol2 = 2 and second time condition is intcol2<>2
WHAT SHOULD I DO NOW ?

If value in a condition became dynamic you can use COALESCE or CASE.

If you want to change the column name dynamically, you must go to Dynamic Sql. i.e forming a query as string and execute.

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

lionofdezert
Aged Yak Warrior

885 Posts

Posted - 2009-08-10 : 05:56:24

query as string..... What i want to avoid

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources