Dynamic conditions WHERE clause Performance SQL Server

Sometimes we need to write WHERE predicate dynamically. For example: Let assume there is business need to filter data on the basis of ntUserID when @btIsUser = 1 and on the basis of ntPostID when @btIsUser = 0.
This script with dymaical predicate is one of the solution of this requirement:

DECLARE @btIsUser AS BIT = 1

DECLARE @ntID AS BIGINT = 100

SELECT *

FROM tblComment

WHERE (

          (

              @btIsUser = 1

              AND ntUserID = @ntID

              )

          OR (

              @btIsUser = 0

              AND ntPostID = @ntID

              )

          )

Total logical read: 64781

This script work fine except it is not able to use indexes efficiently those are created with key column ntUserID and ntPostID due to logical operator OR. Due to which it has very high number of logical read.

Best practice: Avoid using Logical opearors “OR” and “NOT” in the WHERE,ON and HAVING caluses.

Altenatively we can write same script in following way:

DECLARE @btIsUser AS BIT = 1

DECLARE @ntID AS BIGINT = 100

IF @btIsUser = 1

     SELECT *

     FROM tblComment

     WHERE ntUserID = @ntID

ELSE

     SELECT *

     FROM tblComment

     WHERE ntPostID = @ntID

 

Total logical read: 5

Query cost comparion :

We can see there is huge difference of query cost. This diiference is due to first script is not able to perfom index seek oprtaion.

Note: If number of IF-ELSE statements are significanly very large to wite an equivalent script then use Dynamic-SQL.