Conditionally adding AND clause to where in SQL Select statement

Question

I searched everything and found questions about the conditional where clauses, but reported nothing about the conditional addition of the fragment andto where clause. I am not a sql / database user, I'm from another area ITthat was thrown into the “try to fix it” problem today because our regular database users have disappeared. I have basic concepts and knowledge in the field of programming, but sqlfor me it's just a strange animal.

I have a simple one:

SELECT abc FROM table WHERE sdate=@somedt

But I need to add:

AND otherdt=@otherdt

but only if @otherdtnot null. If it is zero, I don’t want to add a part at all and. I tried several ways to do this, but it keeps giving me an error. Any help is appreciated. This was my last unsuccessful attempt:

SELECT abc FROM table 
WHERE sdate=@somedt
IF @otherdt IS NOT NULL
BEGIN
AND otherdt=@otherdt
END

Answer 1

SELECT abc FROM table 
WHERE sdate = @somedt AND (@otherdt IS NULL OR otherdt = @otherdt)

Answer 2

You can use this condition:

SELECT abc FROM table 
WHERE sdate=@somedt AND (@otherdt IS NULL OR otherdt = @otherdt)

Answer 3

DECLARE @Sql NVARCHAR(MAX)
       ,@somedt DATETIME 
       ,@otherdt DATETIME

SET @Sql = N' SELECT abc 
              FROM table 
              WHERE sdate = @somedt '
        + CASE WHEN  @otherdt IS NOT NULL 
                 THEN N' AND otherdt = @otherdt'
             ELSE N'' END

Exec sp_executesql @Sql 
                  ,N'@somedt DATETIME , @otherdt DATETIME'
                  ,@somedt
                  ,@otherdt

Answer 4

You can use UNIONone that in many cases works better than ORqueries.

SELECT abc FROM table
WHERE sdate=@somedt AND @otherdt IS NULL
UNION
SELECT abc FROM table
WHERE sdate=@somedt AND otherdt = @otherdt