Q: MsSQL dynamic Search with lot of conditions

Hi there!

Over the past few years I've worked on a number of websites. I've implemented a Search with varying conditions many times, but now it's different. So far the Search used stored procedures and dynamic SQL:

 DECLARE @select_fields NVARCHAR(1024)
 DECLARE @where_clause NVARCHAR(4000)
 DECLARE @join_clause NVARCHAR(2048)
 DECLARE @query NVARCHAR(4000)
 
 SET @select_fields = '...'
 SET @join_clause = '' 
 SET @where_clause = ' WHERE 1 = 1'
 
 ...
 
 IF @in_field1 IS NOT NULL
  SET @where_clause = @where_clause + ' AND [table1].[field1] = @in_field1'
  
 IF @in_field2 IS NOT NULL
  SET @where_clause = @where_clause + ' AND [table2].[field2] = @in_field2'
  
 ...
 
 SET @query = 'SELECT' + @select_fields + ' FROM [table1] AS [t1]' + @join_clause + @where_clause
 
 DECLARE @params_def NVARCHAR(1024)
 
 SET @params_def = '@in_field1 TYPE, @in_field2 TYPE, ...'
 
 EXEC sp_executesql @query, @params_def,
  @in_field1 TYPE,
  @in_field2,
  ...

well *WHAT* is beeing searched?

for example:  large text items?

names?

or are you letting users pick stuff ?

and I'd have to question your "35-40 conditions
"
is this done by a program say like a planned report or it this done by a user?

most of the time humans can't manage more than about 10 items at a time.... more than that tends to be confused / forgoten etc..


one thing if you have not looked at it before is

http://www.microsoft.com/sql/evaluation/features/fulltext.mspx
 full text search.

depending on what you have this may or may not help.


but I'd tend to say that if you have 40 "Possible" items that you will be able to find that you may have say 90% of the cases where users will only care about 1-9 items 10-12 max
and that only a very small number of times will most users want to combine say 20 terms.

Hi figuerres!

Thank you for your reply.
The data beeing searched is nvarchar, ntext (without fulltext), datetime, bit, int and uniqueidentifier .

The system needs to be flexible. If the user can choose from 40 conditions, there should be a solution for 40 conditions .

The problem is that nvarchar is only 4 000 characters long and the generated SQL is about 10 000 characters long (with 27 conditions).

Thank you.
Stanimir

in the text data are you seaching large text bodies for words / phrases or small coulmns that match a value?

for the rest....

I think you can build larger sql expressions but it's a hassle... let me look up some info...

have you tried:

declare @SQL1 nvarchaar(4000), @SQL2 nvarchar(4000), @SQL3 nvarchar(4000)

exec(@SQL1+@SQL2+@SQL3)

??

Thank you. I'll try it - but I think there should be a better approach.

One other solution is to use COALESCE, but the problem is that I'm passing an array (as nvarchar, something like: GUID:VALUE,GUID:VALUE, ...) and I need dynamic SQL to parse the nvarchar to an array and build the WHERE clause (the array is one of the many parameters ).

You could build a temporary table holding the GUID/VALUE pairs.  Then your query could be of the form WHERE a IN (SELECT ... from #temporarytable)

Hi Maurits!

Thank you for your reply. I could use a temporary table, but imagine that the user supplied only the first 2 conditions or only the last 2 conditions. Let's look at the second case - the first part of the search will return > 200 000 records.

Thanks,
Stanimir.

Use temporary tables on big queries. Like select the data for the first i.e. 20 conditions into a temp table and then search on the rest on that. If it less than 20, use just one.

Or something like that.