Dynamic SQL WHERE Clauses without sp_executesql

I was asked again today about doing dynamic where clauses without using sp_executesql. See below for my previous answer (19/10/2005). The only thing I would suggest now is to improve my suggestion below is to make the statements even more readable with the ISNULL() statment:

e.g. AND USERNAME >= isnull(@FromUser , uSERNAME). See below for more details.

---ORIGINAL RESPONSE---

I used this a few years ago at Kanes Hire, but I found it quite useful again at Multibase:

If you have a search screen, but want to show ALL records if a parameter is NULL, you can use the following (without IF…THEN conditional logic):

(The trick is that the case statements always resolve to true when the parameter is NULL) – so the criteria is effectively ignored, just like an “ALL”. Seems to perform well on the small queries I used here.

SQL:
AND UserName >= CASE WHEN @FromUser IS NULL THEN UserName ELSE @FromUser END


CREATE PROCEDURE up_StampingReport
@FromDate datetime,
@ToDate datetime,
@FromUser varchar(255),
@ToUser varchar(255),
@IsOutsideSLA varchar(255),
@Rework_Count int,
@TransactionType_ID int,
@IsMatterWithClient char(1),
@IsMatterWithPerpetual char(1)

AS

SELECT *
FROM VW_STAMPREPORT
WHERE
(Expected_datetime >= @FromDate AND Expected_datetime <= @ToDate) AND UserName >= CASE WHEN @FromUser IS NULL THEN UserName ELSE @FromUser END
AND UserName <= CASE WHEN @ToUser IS NULL THEN UserName ELSE @ToUser END AND IsOutsideSLA = CASE WHEN @IsOutsideSLA IS NULL THEN IsOutsideSLA ELSE @IsOutsideSLA END AND Rework_Count = CASE WHEN @Rework_Count IS NULL THEN Rework_Count ELSE @Rework_Count END AND TransactionType_Id = CASE WHEN @TransactionType_ID IS NULL THEN TransactionType_Id ELSE @TransactionType_ID END AND IsMatterWithClient = CASE WHEN @IsMatterWithClient IS NULL THEN IsMatterWithClient ELSE @IsMatterWithClient END AND IsMatterWithPerpetual = CASE WHEN @IsMatterWithPerpetual IS NULL THEN IsMatterWithPerpetual ELSE @IsMatterWithPerpetual END GO