URGENT T- SQL QUESTION. NEED HELP.

  • There is a Table.

    CREATE TABLE TBLCOLOR

    (ID INT, COLOR VARCHAR(20))

    Insert into TBLCOLOR

    SELECT 1,'RED'

    UNION ALL

    SELECT 2,'BLUE'

    UNION ALL

    SELECT 3,NULL

    UNION ALL

    SELECT 4,'GREEN

    UNION ALL

    SELECT 5,'BLACK'

    UNION ALL

    SELECT 6,NULL

    SELECT * FROM TBLCOLOR

    ------------------------------

    ID COLOR

    1 RED

    2 BLUE

    3 NULL

    4 GREEN

    5 BLACK

    6 NULL

    NOW,DELCARE A VARIABLE @COLOR VARCHAR(20)

    I.E. DECLARE @COLOR VARCHAR(20);

    MY QUESTION IS TO PUT A WHERE CLAUSE IN ABOVE SELECT STATEMENT

    THAT RUNS LIKE THIS:

    1. RETURNS ROW WHEN COLOR IS MATCHED IN @COLOR.

    LIKE

    -------------

    ID COLOR

    1 RED ---->>>> IF @COLOR='RED'

    1 BLUE ---->>>> IF @COLOR='BLUE'

    AND SO ON.

    2. RETURNS ALL ROWS IF @COLOR=NULL

    I.E.

    ------------------------------

    ID COLOR

    1 RED

    2 BLUE

    3 NULL

    4 GREEN

    5 BLACK

    6 NULL

    NOTE: I KNOW HOW TO DO THIS USING CASE STATEMENT, BUT THIS IS POSSIBLE THROUGH COALESCE AND ISNULL. BUT DONT KNOW HOW TO DO IT.

    ALL SQL SERVER MASTERS,, PLEASE HELP....!!! IT'S URGENT.

  • This is probably what you are after

    http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/

  • Thanks SSC journeyman,,,,,,,,,,,,,,,,,,,,,

    Where condition was good,,,,

    But need some extra help also......

    how to do it using coalesce and is null function. Please Sir.

  • Tried out this:

    DECLARE @COLOR VARCHAR(20);

    SET @Color=NULL

    select ID,Color from TBLCOLOR

    WHERE ISNULL(Color,'') like '%'+ISNULL(@COLOR,'')

  • Is this what you are looking for?

    ---Set paramter to NULL

    begin

    declare @COLOR VARCHAR(20);

    set @COLOR = NULL

    select id,color from TBLCOLOR where color in

    (select color from TBLCOLOR where color = COALESCE(@COLOR,color))

    or isnull(color, 0) = isnull(@COLOR,0)

    end

    Output:

    id color

    1 RED

    2 BLUE

    3

    4 GREEN

    5 BLACK

    6

    -----------------------------------------------------

    --Set parameter value to RED---

    begin

    declare @COLOR VARCHAR(20);

    set @COLOR = 'RED'

    select id,color from TBLCOLOR where color in

    (select color from TBLCOLOR where color = COALESCE(@COLOR,color))

    or isnull(color, 0) = isnull(@COLOR,0)

    end

    Output:

    id color

    1 RED

  • declare @color varchar(20)

    set @color = ''

    SELECT * FROm TBLCOLOR

    Where color = coalesce(NULLIF(@color,''),color)

    When no value is entered, the value passed would be ‘’ . And the NULLIF returns a NULL value as both the values are now equal. The arguments to the COALESCE would hence be ( NULL,color). And the function would take the first not NULL value and return the entire set.

  • NO,

    If @color=null,,, then

    ID COLOR

    1 red

    2 blue

    3 null

    4 black

    5 green

    6 null

    wants to retrieve NUll rows also when @color is supplied null;

    and if @color='red'

    then matching row i.e.

    ID COLOR

    1 RED

  • No Arun,,,,,,

    i do not supply @color=''

    i supply @color=null

    your query will give

    ID COLOR

    1 RED

    2 BLUE

    4 GREEN

    5 BLACK

    but will not give

    3 NULL

    5 NULL.

    i want to retrieve all the row including NULL.

    i want my query to use BOTH ISNULL and COALESCE..

    Thanks in advance.

  • begin

    declare @COLOR VARCHAR(20);

    set @COLOR = NULL

    select id,ISNULL(color,'NULL') color from TBLCOLOR where color in

    (select color from TBLCOLOR where color = COALESCE(@COLOR,color))

    or isnull(color, 0) = isnull(@COLOR,0)

    end

    OUTPUT:

    id color

    1 RED

    2 BLUE

    3 NULL

    4 GREEN

    5 BLACK

    6 NULL

  • Personally I would try an avoide the COALESCE and ISNULL funtion on a where clause

    the simplest way

    SELECT Id,Color

    From TBLCOLOR

    WHERE Color=@Color OR @Color is NULL

    with IsNULL function

    SELECT Id,Color

    From TBLCOLOR

    WHERE IsNull(Color,'')=ISNULL(@Color,'')

    with COALESCE function

    SELECT Id,Color

    From TBLCOLOR

    WHERE COALESCE(Color,'')=COALESCE(@Color,Color,'')

    Personally I would go for the first option or use a catch all query for a complex where clause.

    Edit : Spelt Colour correctly on one side of the query.

    Queries reformed for is null and coalsece

    _________________________________________________________________________
    SSC Guide to Posting and Best Practices

  • Thanks a lot dgowrij,,, your query Works.....

    But i dont want to use Derived Table. just simple where clause with one equi.

    like: where color=@color,,,,,, using isnull and coalesce both in the this where clause.

    I think u know what i m asking 😛 🙂

  • Try this one !

    DECLARE @COLOR VARCHAR(20);

    SET @COLOR = NULL

    SELECT * FROM TBLCOLOR

    WHERE COALESCE (COLOR, '') = COALESCE (@COLOR, COLOR, '');

    But its not optimized.

  • Hii Jason, Thanks for ur reply.

    Your first query works great..

    But your 2nd and 3rd one does not serve the purpose. They skip the Null Rows.

    Thanks Again.

  • Thanks uravindarreddy,,, Ur query is Great.

    But Can U explain its working to me.,? I m a bit naive to SQL , cant understand its in depth working.!!

    Thanks again for ur help.

  • Sorry I spotted that, and just changed them to work. Theres still a problem with the ISNULL though.

    this is the isnull

    SELECT ID,COLOR

    FROM #TBLCOLOR

    WHERE ISNULL(COLOR,'')=ISNULL(@COLOR,ISNULL(COLOR,''))

    _________________________________________________________________________
    SSC Guide to Posting and Best Practices

Viewing 15 posts - 1 through 15 (of 22 total)

You must be logged in to reply to this topic. Login to reply