HELP ME WITH THE WHERE CLAUSE ( Select records based on a variable value )

• mw_sql_developer

  SSCoach

  Points: 19445

  More actions

  June 26, 2018 at 11:47 am

  #355798

  My Issue:
  I want all the records or ( The ones where  INCLUDE_ME = 'Y' ) to show up based on the value in the variable @INCLUDE_ME 
  One one occasion I would like only to see the ones whefre INCLUDE_ME = 'Y'
  On another occasion I would like to display all records. I would not mind changing the data type in the column INCLUDE_ME  to INT ( if needed )

  Ok.. Smart Brains.. Lets see who can help me.

  Declare @INCLUDE_ME CHAR(1) = 'Y'
Declare @tab table ( NAME VARCHAR(20), INCLUDE_ME CHAR(1) ) ;

  INSERT INTO @tab( NAME, INCLUDE_ME )
Select 'ABC' as NAME , 'Y' as INCLUDE_ME
UNION
Select 'CDC' as NAME , 'N' as INCLUDE_ME
UNION
Select 'XXX' as NAME , 'Y' as INCLUDE_ME
;

  SELECt *
FROM
@tab
WHERE
CASE WHEN @INCLUDE_ME = 'Y' THEN 1 ELSE 0 END = 1

• mw_sql_developer

  SSCoach

  Points: 19445

  More actions

  June 26, 2018 at 11:54 am

  #1995338

  mw_sql_developer - Tuesday, June 26, 2018 11:47 AM

  My Issue:
  I want all the records or ( The ones where  INCLUDE_ME = 'Y' ) to show up based on the value in the variable @INCLUDE_ME 
  One one occasion I would like only to see the ones whefre INCLUDE_ME = 'Y'
  On another occasion I would like to display all records. I would not mind changing the data type in the column INCLUDE_ME  to INT ( if needed )

  Ok.. Smart Brains.. Lets see who can help me.

  Declare @INCLUDE_ME CHAR(1) = 'Y'
Declare @tab table ( NAME VARCHAR(20), INCLUDE_ME CHAR(1) ) ;

  INSERT INTO @tab( NAME, INCLUDE_ME )
Select 'ABC' as NAME , 'Y' as INCLUDE_ME
UNION
Select 'CDC' as NAME , 'N' as INCLUDE_ME
UNION
Select 'XXX' as NAME , 'Y' as INCLUDE_ME
;

  SELECt *
FROM
@tab
WHERE
CASE WHEN @INCLUDE_ME = 'Y' THEN 1 ELSE 0 END = 1

  No need, managed to figure out on my own....Thanks for taking time to read

  
Declare @INCLUDE_ME INT= 1 ;
Declare @tab table ( NAME VARCHAR(20), INCLUDE_ME INT ) ;

  INSERT INTO @tab( NAME, INCLUDE_ME )
Select 'ABC' as NAME , 1 as INCLUDE_ME
UNION
Select 'CDC' as NAME , 0 as INCLUDE_ME
UNION
Select 'XXX' as NAME , 1 as INCLUDE_ME
;

  SELECt *
FROM
@tab
WHERE
CASE WHEN @INCLUDE_ME = 1 THEN (INCLUDE_ME/1) ELSE LEN(INCLUDE_ME) END = 1


• Luis Cazares

  SSC Guru

  Points: 183706

  More actions

  June 26, 2018 at 11:58 am

  #1995340

  Try building a table of truth with the possible options. Once you have it, the statement should be fairly easy to write.

  Luis C.
  General Disclaimer:
  Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

  How to post data/code on a forum to get the best help: Option 1 / Option 2

• Luis Cazares

  SSC Guru

  Points: 183706

  More actions

  June 26, 2018 at 11:59 am

  #1995342

  mw_sql_developer - Tuesday, June 26, 2018 11:54 AM

  mw_sql_developer - Tuesday, June 26, 2018 11:47 AM

  My Issue:
  I want all the records or ( The ones where  INCLUDE_ME = 'Y' ) to show up based on the value in the variable @INCLUDE_ME 
  One one occasion I would like only to see the ones whefre INCLUDE_ME = 'Y'
  On another occasion I would like to display all records. I would not mind changing the data type in the column INCLUDE_ME  to INT ( if needed )

  Ok.. Smart Brains.. Lets see who can help me.

  Declare @INCLUDE_ME CHAR(1) = 'Y'
Declare @tab table ( NAME VARCHAR(20), INCLUDE_ME CHAR(1) ) ;

  INSERT INTO @tab( NAME, INCLUDE_ME )
Select 'ABC' as NAME , 'Y' as INCLUDE_ME
UNION
Select 'CDC' as NAME , 'N' as INCLUDE_ME
UNION
Select 'XXX' as NAME , 'Y' as INCLUDE_ME
;

  SELECt *
FROM
@tab
WHERE
CASE WHEN @INCLUDE_ME = 'Y' THEN 1 ELSE 0 END = 1

  No need, managed to figure out on my own....Thanks for taking time to read

  
Declare @INCLUDE_ME INT= 1 ;
Declare @tab table ( NAME VARCHAR(20), INCLUDE_ME INT ) ;

  INSERT INTO @tab( NAME, INCLUDE_ME )
Select 'ABC' as NAME , 1 as INCLUDE_ME
UNION
Select 'CDC' as NAME , 0 as INCLUDE_ME
UNION
Select 'XXX' as NAME , 1 as INCLUDE_ME
;

  SELECt *
FROM
@tab
WHERE
CASE WHEN @INCLUDE_ME = 1 THEN (INCLUDE_ME/1) ELSE LEN(INCLUDE_ME) END = 1


  Can you try to create a more expensive option? That doesn't seem to be slow enough.
  Keep it simple and avoid the CASE expression.

  Luis C.
  General Disclaimer:
  Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

  How to post data/code on a forum to get the best help: Option 1 / Option 2

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  June 26, 2018 at 12:12 pm

  #1995346

  A simple catch all query will work
  
SELECT *
FROM @tab AS [t]
WHERE ([t].[INCLUDE_ME] = @INCLUDE_ME) OR (@INCLUDE_ME <> 'Y');


• Luis Cazares

  SSC Guru

  Points: 183706

  More actions

  June 26, 2018 at 12:28 pm

  #1995348

  Lynn Pettis - Tuesday, June 26, 2018 12:12 PM

  A simple catch all query will work
  
SELECT *
FROM @tab AS [t]
WHERE ([t].[INCLUDE_ME] = @INCLUDE_ME) OR (@INCLUDE_ME <> 'Y');


  Let him think or fail on his own.

  Luis C.
  General Disclaimer:
  Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

  How to post data/code on a forum to get the best help: Option 1 / Option 2