locked
case statement for where clause RRS feed

  • Question

  • Hi Experts,

    Exclude or include where clause based on parameter value,

    CREATE TABLE #TEMP
    (
    	ID	CHAR(1)
    )
    
    INSERT INTO #TEMP VALUES ( '1' )
    INSERT INTO #TEMP VALUES ( '2' )
    INSERT INTO #TEMP VALUES ( '3' )
    
    DECLARE	@ID	CHAR(1)
    SET		@ID	=	''--'2'
    
    SELECT	*
    FROM	#TEMP
    WHERE	1 = CASE WHEN LEN(@ID) <> 0 THEN @ID ELSE 1 END
    
    DROP TABLE #TEMP
    

    Any help?

    Thanks

    Kumar


    Please do let us know your feedback. Thank You - KG, MCTS

    Thursday, April 3, 2014 9:07 PM

Answers

  • SELECT * FROM #TEMP WHERE 1 = CASE WHEN LEN(@ID)= 0 THEN 1

    ELSE case when Id =@ID THEN 1 ELSE 0 END END


    Why does it have to be CASE? Why not build the query dynamically?

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles


    • Edited by Naomi N Thursday, April 3, 2014 9:21 PM
    • Marked as answer by gk1393 Thursday, April 3, 2014 9:22 PM
    Thursday, April 3, 2014 9:20 PM

All replies

  • SELECT * FROM #TEMP WHERE 1 = CASE WHEN LEN(@ID)= 0 THEN 1

    ELSE case when Id =@ID THEN 1 ELSE 0 END END


    Why does it have to be CASE? Why not build the query dynamically?

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles


    • Edited by Naomi N Thursday, April 3, 2014 9:21 PM
    • Marked as answer by gk1393 Thursday, April 3, 2014 9:22 PM
    Thursday, April 3, 2014 9:20 PM
  • CREATE TABLE #TEMP
    (
    	ID	CHAR(1)
    )
    
    INSERT INTO #TEMP VALUES ( '1' )
    INSERT INTO #TEMP VALUES ( '2' )
    INSERT INTO #TEMP VALUES ( '3' )
    
    DECLARE	@ID	CHAR(1)
    SET @ID	= '' 
     
    SELECT	*
    FROM	#TEMP
    WHERE ID= @ID or @ID =''
    
    DROP TABLE #TEMP

    Thursday, April 3, 2014 10:14 PM
  • SQL has no CASE statement. We have a CASE expression; expression return scalar values. This has nothing to do SQL; it is a fundamental programming concept. You do not know how to program in any language. 

    >> Exclude or include where clause based on parameter value,<<

    Another fundamental error. You never had a software engineering course, so you do not know about coupling and cohesion. A module of code should have one and only entry point, one and only one exit point, and perform one and only one clearly defined task. What you want is called an “Automobile, Squids and Lady Gaga” module whose function is controlled externally by a flag. 

    You do not know how to program in any language. And you do not know SQL in particular. A table has to have a key by definition. We have a row constructor in SQL for insertion. 

    CREATE TABLE Foobar
    (foo_id CHAR(1) NOT NULL PRIMARY KEY);  -- only possible DDL

    INSERT INTO Foobar 
    VALUES ('1'), ('2'), ('3'); 

    In SQL, we use a NULL for missing or unknown values. Do you understand that SQL pads CHAR(n) with right hand blanks? The empty string becomes a space in your code. 

    SELECT foo_id
    FROM Foobar
    WHERE COALESCE (@in_parm, foo_id) = foo_id;


    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Friday, April 4, 2014 12:15 AM