I want to add an if else statement below the where statement.

[patrick]

Below IMAGE is the DB TABLE.

I want to add an if else statement below the where statement.

ALTER PROCEDURE [dbo].[usp_PROCEDURE]

    @startdate varchar(8) = NULL,
    @enddate varchar(8) = NULL
AS
BEGIN


.
.

wher id = dm.id
and

if ( @startdate == NULL && @enddate == NULL)

{

    CR IS NULL AND UP IS NULL

}

else if ( @startdate == NULL && @enddate == 2023)

{

    CR IS NULL AND UP = '2023'
}

else if ( @startdate == 2023 && @enddate == NULL)

{

    CR = '2023' AND UP IS NULL
}

else if ( @startdate == 20230101 && @enddate == 20230505)

{

    (( CONVERT(DATE, dm.CR) BETWEEN CONVERT(DATE,@startdate) AND (CONVERT(DATE, @enddate))) or ( CONVERT(DATE, dm.UP) BETWEEN CONVERT(DATE, @startdate) AND (CONVERT(DATE, @enddate))))

}

and id.name = a.name

END

Can I change it like below??

ERROR ===> then CR is null and UP is null

 and CASE WHEN @startdate is null and @enddate is null then CR is null and UP is null
 and CASE WHEN @startdate is null and @enddate is not null then CR is null and UP = @enddate
 and CASE WHEN @startdate is not null and @enddate is null then CR = @startdate and UP is null
 and CASE WHEN @startdate is not null and @enddate is not null then (( CONVERT(DATE, CR) BETWEEN CONVERT(DATE,@startdate) AND (CONVERT(DATE, @enddate))) or ( CONVERT(DATE, UP) BETWEEN CONVERT(DATE, @startdate) AND (CONVERT(DATE, @enddate))))

 

[jmcilhinney]

Please explain what you're actually trying to achieve, rather than expecting us to work it out from code that doesn't do it.

 

[Skydiver]

As an aside, I'm not seeing how this is a C# question. Looks to be purely a SQL question.

 

[Skydiver]

@patrick : I put your SQL code into CODE tags to make them easier to read. In the future please do the following:

• Always put your code in code tags
• Have a clear explanation of what you are trying to do and why

I had to stare at that question for a very very long time to finally figure out what @patrick is trying to ask for. He wants the lines 19, 27, or 34 to become part of his WHERE conditions, but only when the other conditions on lines 15, 23, and 30 are true. If none of those other conditions are true, then he wants line 41 to become part of his WHERE condition.

In my mind, this just seem like a judicious use of parenthesis, AND, and OR should be sufficient to get the effect that he wants. Also probably a lot of indenting will also help people read the SQL code. Something that looks like:

:
WHERE
    ID = DM.ID AND
    (
        (@startdate IS NULL AND @enddate IS NULL AND CR is null and UP is null) OR
        (@startdate IS NULL AND @enddate == 2023 AND CR IS NULL AND UP = '2023') OR
        :
    ) AND
    ID.name = A.name
:

It's still unclear to me why @patrick wants/needs to switch to using a CASE statement. It's also not clear why the stored procedure inputs cannot be sanitized, or the table data be normalized, or the logic be changed, or a combination of the previous three options.

 

[cjard]

Looks to me like your life would be a lot easier if you just passed DateTime.MinValue for @startDate instead of NULL, DateTime.MaxValue for @endDate, instead of NULL

Baking hardcoded values into your code is a silly idea that creates an ongoing maintenance headache

Performing conversions on table data in a where clause is also a silly idea as it disables use of indexes in most query scenarios