Hello there!<br /><br />The problem I am facing is probably a very common one but I haven't found any elegant solution out there so I decided to post this.<br /><br />PROBLEM<br /><br />** I need to implement a multi criteria search across multiple tables (~4).<br />** Search routine is implemented as a stored procedure that takes search criteria as input parameters <br />** Any number of search criteria can be NULL, thus we are looking for dynamic queries that can adjust accordingly<br />** The task is performance critical<br />** We run SQL Server 2005<br /><br />IDEAS<br /><br /> The first approach I took is a very basic SELECT statement with tons of INNER JOINS. The problem with NULL search criteria is solved by using COALESCE statement like this<br /><br /> <pre id="code"><font face="courier" size="2" id="code"><br /> --inside one of the joins<br /> INNER JOIN SomeTable st ON st.id = basic_table.id AND<br /> st.field_name = COALESCE(@search_param,st.field_name)<br /> </font id="code"></pre id="code"><br /> <br /> Now this works all right. Kind of <img src='/community/emoticons/emotion-1.gif' alt='' />. I have two problems with this. First of, users that match multiple search criteria get duplicated in the result set. Last but not least, I think there's something really wrong with this whole approach since I have a total of 6 joins in my SELECT to get the dataset I need and perform the search. <br /><br /> I am considering performing some tricky filtering that can give me good performance. I was looking into Common table expressions hoping to build a nested WITH that will filter records and return the desired dataset in the outermost SELECT, but as it turned out, you cannot use nested WITH so I am stuck for right now.<br /><br />Could anyone provide any ideas concernig this kind of task?