Boy, you guys are brave!

I NEVER use dynamic SQL EVER in ANY Production system.

It does come in handy for 1-off utilities that I write and use myself.

For Queries that return the same result set and the predicate varies (the WHERE Clause) I use a stored procedure that accepts all of the search attributes (and defaults them to NULL if not passed). The sp has a single hard coded query with any join logic required to return the attributes in the result set. The key is that it also contains a join to a User Defined Function that accepts the search criteria as input parameters. This UDF returns a single column table containing the PK values that meet the search criteria and restricts rows returned to only those matching the search criteria.

Here is a simple example:

SELECT c.FirstName

,c.LastName

,c.Address1

,ca.Address2

,ca.City

,ca.State

,ca.Zip

,HomePhone = cp.PhoneNumber

FROM dbo.Customer c

WITH (NOLOCK)

JOIN dbo.CustomerAddress ca

WITH (NOLOCK)

ON c.CustomerID = ca.CustomerID

LEFT OUTER

JOIN CustomerPhone cp

WITH (NOLOCK)

ON c.CustomerID = cp.CustomerID

AND cp.PhoneType = 'Home'

-- This join is used instead of a WHERE clause to limit results based on input parameters

JOIN dbo.udf_CustomerSearch(@Lastname

,@City

,@PhoneNumber) k

ON c.CustomerID = k.CustomerID

Be careful out there.