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.
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.
AND UserName >= CASE WHEN @FromUser IS NULL THEN UserName ELSE @FromUser END
CREATE PROCEDURE up_StampingReport
(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