Implementing a Dynamic WHERE Clause in SQL Server 2000.

I’m working on my Code Generator so it will be able to generate stored procedures for me. Until now I generated the SQL queries in my C# code, but now, due to a development request(\demand) from my client I must work with stored procedures.

The first thing I thought about is how to make my search pages easy to upgrade and maintain if I’m going to use SP(stored procedure). Now, when I need to add another Field to my dynamic where clause, it’s quite simple – I’m building the query in my data ccess object according to the parameters and everything is OK. I feared that by using SP for my search pages, I’ll need to call something like EXEC which looks like a joke – If you must call EXEC in the SP, you better put the SQL in your C# code and get it over with.

After doing some searches, I found this article which present a way to build and execute dynamic where clauses with “COALESCE” function. After I’ve searched a little more, I found a similar way to do the same thing with better performance:

” I check the value against NULL and achieve good performance and flexibility. No problem with LIKE values either:

WHERE
(@title IS NULL OR title LIKE @title)
AND
(@min_release_date IS NULL OR release_date >= @min_release_date)
AND
(@document_id IS NULL or document_id = @document_id)

Great performance and flexibility! All indexes are used as expected. ” (Zelk)

Looks great !
If the sent parameter is null, don’t “cut” the results by the parameter, else – use it…

OK, I must dig into my generator and start implementing this…