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.