One way would be to create a stored procedure like:
ALTER PROCEDURE dbo.usp_SomeProc
@TrxStartDate DATETIME,
@TrxEndDate DATETIME,
@PersonValue VARCHAR(10) = '',
@ExpenseValue VARCHAR(10) = '',
@UnitValue VARCHAR(10) = ''
AS
BEGIN
SET NOCOUNT ON;
DECLARE @Sql VARCHAR(MAX)
SET @Sql = 'SELECT *
FROM TRXTransaction
INNER JOIN EXPExpense ON EXPExpenseID = TRXEXPID
INNER JOIN SUBContractor ON SUBConID = TRXSCID
LEFT JOIN UNIUnits ON UNIID = TRXUNIID
WHERE (SUBConID LIKE ''%' + @PersonValue + '%'')
AND (TRXDate BETWEEN CONVERT(DateTime, ''' + CONVERT(VARCHAR, ISNULL(@TrxStartDate, GETDATE())) + ''') AND CONVERT(DATETIME, ''' + CONVERT(VARCHAR, ISNULL(@TrxEndDate, GETDATE())) + '''))'
IF (NOT @ExpenseValue = '')
SET @Sql = @Sql + ' AND (EXPExpenseID LIKE ''%' + @ExpenseValue + '%'')'
IF (NOT @UnitValue = '')
SET @Sql = @Sql + ' AND (UNIID LIKE ''%' + @UnitValue + '%'')'
SET @Sql = @Sql + ' ORDER BY ORDER BY SUBConTitle, TRXDate ASC'
PRINT @Sql
END
In your code you would call the SP but passing in all values from each DDL even if the values are not selected. These should be passed into the SQL Command using SQL Parameters. This reduces the risk of SQL injection. Secondly you can maintain dynamic queries even after your application has been deployed.
Now I am not going to go into the why's and where's of dynamic sql. There are uses for it. There are also things to avoid. You could easily do this in code using a string builder and or the string.format using parametrised text. I dont see how you could write 27 if elses. 3 maybe or 4 at the most but not 27.
You dont even have to write a proc if this causes concern. just an idea.