CODEWITHSUNDEEP
sql-servert-sql
user3607582
user3607582

Reputation: 79

Multiple Condition in Where Clause for the same column

How do I write a MS SQL statement for the below condition?

I have a form that allows users to enter dates (fromDate & toDate) and ID (fromID & toID) in range. The fields can be blank for all OR enter only either from or to field OR enter both from and to fields. Selection is based on the entered values to select. Below are the conditions checking in where clause for value entered.

no value entered => skip all conditions

value entered in fromDate only => Date = frDate

value entered in toDate only => Date <= toDate

value entered in both fromDate & toDate => Date between fromDate and toDate

Condition is applied to ID field as well.

Any advice is highly appreciated. Thanks in advance.

Upvotes: 1

Views: 2563

Answers (3)

Karan
Karan

Reputation: 121

You can use IS NULL to check param has value 

SELECT * FROM Table
    WHERE (@FromDate IS NULL OR Date > @FromDate) AND (@ToDate IS NULL OR Date < @ToDate)

Same type of query will be used for Id

Upvotes: 1

Neha
Neha

Reputation: 197

Below is the EXAMPLE and not the exact query you can try to put it in your way using CASE statements

SELECT values     
FROM Table   
WHERE CASE 
     WHEN fromDate = null & todate = null THEN 
     WHEN fromDate != null & toDate != null THEN Date between fromDate and toDate
     WHEN fromDate != null THEN  Date = frDate
     WHEN toDate != null THEN Date = toDate

Upvotes: 0

Md. Zakir Hossain
Md. Zakir Hossain

Reputation: 1092

You can solve your problem using dynamic query. Your question is not fully clear. Here i'm giving you a solution which will help you to solve your problem. Try this:

1. Create Dynamic query in a Store Procedure

        CREATE PROCEDURE sp_YourSPName
    /* Input Parameters */
        @FromDate DATETIME ,
        @ToDate DATETIME 
    AS
        SET NOCOUNT ON
        /* Variable Declaration */
        DECLARE @SQLQuery AS NVARCHAR(4000)
        DECLARE @ParamDefinition AS NVARCHAR(2000) 

        /* Build the Transact-SQL String with the input parameters */ 
        SET @SQLQuery = 'Select * From YourTableName where (1=1) ' 

        /* check for the condition and build the WHERE clause accordingly */
        IF (@FromDate IS NOT NULL)
           AND (@ToDate IS NOT NULL)
            SET @SQLQuery = @SQLQuery + 
                ' And (YourDate BETWEEN @FromDate AND @ToDate)'

        IF (@FromDate IS NULL)
           AND (@ToDate IS NOT NULL)
            SET @SQLQuery = @SQLQuery + ' And (YourDate <= @ToDate)' 

        IF (@FromDate IS NOT NULL)
           AND (@ToDate IS NULL)
            SET @SQLQuery = @SQLQuery + ' And (YourDate = @FromDate)'


        /* Specify Parameter Format for all input parameters included 
        in the stmt */
        SET @ParamDefinition = '@StartDate DateTime,
                            @EndDate DateTime'
        /* Execute the Transact-SQL String with all parameter value's  Using sp_executesql Command */

        EXECUTE sp_Executesql @SQLQuery,
             @ParamDefinition,
             @FromDate,
             @ToDate

        IF @@ERROR <> 0
            GOTO ErrorHandler

        SET NOCOUNT OFF
        RETURN(0)

        ErrorHandler :
        RETURN(@@ERROR)
    GO

2. Execute Store Procedure: 

EXEC sp_YourSPName '01 Oct 2018', '01 Oct 2018'

For more info see this link

Upvotes: 1

Related Questions