Dynamic SQL query writing in SQL Server 2005

Question

What is the best way to write a dynamic parametrized query for sql server 2005 where passed parameter value may be null?

Answer 1

Make use of : SP_EXECUTESQL

Answer 2

How about something like

DECLARE @Table TABLE(
        val1 VARCHAR(20),
        val2 VARCHAR(20)
)

DECLARE @Param VARCHAR(20)

INSERT INTO @Table SELECT '1','2'
SELECT  *
FROM    @Table
WHERE   (@Param IS NULL OR val1 = @Param)

However, this will slow down performance. I would recomend that when you build your dynamic query, dont add parameters to the where clause, if it is not requied.

Answer 3

Only if possible:
Write the query with all the parameters included, but instead of writing a regular

WHERE
    field1=@param1
    and field2=@param2
    .....

write

WHERE
    (@param1 is null or field1=@param1)
    and (@param2 is null or field2=@param2)
    ...

or

WHERE
    field1=isnull(@param1,field1)
    and field2=isnull(@param2,field2)
    ...

but if serious performance issues occur, consider writing the query for each single case.
ONLY if this query executes many many times per second on small tables, there is a reason to write it entirely, use sp_prepare/sp_execute for it, so it will run fastest in this case.