Database

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,
...

But now I'm facing an application that is requiring an advanced search with about 35-40 conditions, some of them arrays. I can't use the code above, because the @query variable is only 4000 characters long. As you can imagine, 35-40 conditions won't fit in it .

What is the best way to implement this Search using stored procedures? I don't want to build the SQL code in the program code and pass it to the stored procedure.

Thank you.
Stanimir.