CODEWITHSUNDEEP
sqlsql-servert-sql
sonsha
sonsha

Reputation: 165

SQL Server : select all if parameter is not null and one condition is false

I am trying to get data in below code, which has scenarios as below.

  1. when @status = 0 and date is not null then get filtered data.

  2. when @status = 1 and date is not null then select all data.

  3. when @status = 1 and date is null then select all data.

    declare @status int = 0,
        @date datetime = '2018-12-01 09:48:34.343'

select CreatedTime 
from AspNetUsers 
where @date is null 
   or (@status = 1 and CreatedTime > '2018-12-01 09:48:34.343')

Upvotes: 0

Views: 552

Answers (2)

mananjmodi
mananjmodi

Reputation: 1

DECLARE @status INT = 0,
        @date DATETIME = '2018-12-01 09:48:34.343'


DECLARE @RQuery NVARCHAR(MAX)

SET @RQuery = ' WHERE 1 = 1 '

IF @status = 0 AND @date IS NOT NULL
BEGIN
    SET @RQuery =  @status + ' = 0 AND  CreatedTime ' + CAST(@date AS DATE)
END
ELSE IF @status = 1 AND @date IS NOT NULL
BEGIN
    SET @RQuery =  ''
END
ELSE IF @status = 1 AND @date IS  NULL
BEGIN
    SET @RQuery =  ''
END

DECLARE @RQueryFULL NVARCHAR(MAX)
SET @RQueryFULL = ''

SET @RQueryFULL = 'SELECT CreatedTime 
FROM AspNetUsers ' + @RQuery

EXEC @RQueryFULL
Try This

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269703

The logic you describe is handled by:

where (@status = 0 and CreatedTime > @date) or
      (@status = 1)

It is unclear why you have two different variables for this.

Upvotes: 2

Related Questions