build 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

Transact-SQL (2005)

build dynamic where clause

Author

Topic

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2009-11-17 : 07:04:32

Hi,
There is a table called tblFields which holds the fieldNames of a table called tblMain.
Using tblFields, I build the where clause of a sql query with AND in between each field criteria, i.e.

where
field1 = 'xyz'
AND field2 = '123'
AND field3 > '432'
...

Question:
How can I construct tblFields so that I can place the selection of 'OR' or 'AND' in tblFields.
The where clause should pick up the 'OR' and 'AND' from tblFields.

Thank you

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-11-17 : 07:24:48

Read this
www.sommarskog.se/dynamic_sql.html

Madhivanan

Failing to plan is Planning to fail

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2009-11-17 : 07:50:38

Hi,
I think you may have mis-understood me.
Please not ethat I am currently building the sql dynamically.
My question is to do with building the AND/OR
i.e. How can I construct table tblFields which uses the right OR or AND
Thanks

sshelper
Posting Yak Master

216 Posts

Posted - 2009-11-17 : 23:54:48

One option is for you to add another column in your tblFields table that determines if an AND or OR should be used for this particular field. Then when you are building your WHERE clause, first get all your fields that are ORs and create your WHERE clause and put this in a parenthesis, then add your AND fields.

Regards,
SQL Server Helper
http://www.sql-server-helper.com/sql-server-2008/import-export-unknown-column-type-geography-geometry.aspx

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2009-11-18 : 04:09:31

I think what you mean is something like:

(field1="xyz" OR field2="tre" OR field3="123") AND (Field4="jhg" AND field5="ewq")

?
Thanks

sshelper
Posting Yak Master

216 Posts

Posted - 2009-11-18 : 08:35:18

Yes, that's what I mean.

Regards,
SQL Server Helper
http://www.sql-server-helper.com/sql-server-2008/merge-statement-with-table-valued-parameters.aspx

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2009-11-18 : 13:46:37

Thank you

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources