URGENT T- SQL QUESTION. NEED HELP.

Question

Post reply

URGENT T- SQL QUESTION. NEED HELP.

Mr. Kapsicum

SSCertifiable

Points: 6128
More actions
January 29, 2013 at 11:01 pm

#394060

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.

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

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

matak SSCrazy Points: 2674 More actions · Answer 1

This is probably what you are after

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

Mr. Kapsicum SSCertifiable Points: 6128 More actions · Answer 2

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.

Jitendra Jangid SSC Rookie Points: 37 More actions · Answer 3

Tried out this:

DECLARE @COLOR VARCHAR(20);

SET @Color=NULL

select ID,Color from TBLCOLOR

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

dgowrij SSC Veteran Points: 234 More actions · Answer 4

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:

idcolor

1RED

2BLUE

3

4GREEN

5BLACK

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:

idcolor

1RED

arun1_m1 SSCommitted Points: 1949 More actions · Answer 5

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.

Mr. Kapsicum SSCertifiable Points: 6128 More actions · Answer 6

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

Mr. Kapsicum SSCertifiable Points: 6128 More actions · Answer 7

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.

dgowrij SSC Veteran Points: 234 More actions · Answer 8

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:

idcolor

1RED

2BLUE

3NULL

4GREEN

5BLACK

6NULL

Jason-299789 SSC-Insane Points: 21601 More actions · Answer 9

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

Mr. Kapsicum SSCertifiable Points: 6128 More actions · Answer 10

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 😛 🙂

uravindarreddy Ten Centuries Points: 1390 More actions · Answer 11

Try this one !

DECLARE @COLOR VARCHAR(20);

SET @COLOR = NULL

SELECT * FROM TBLCOLOR

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

But its not optimized.

Mr. Kapsicum SSCertifiable Points: 6128 More actions · Answer 12

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.

Mr. Kapsicum SSCertifiable Points: 6128 More actions · Answer 13

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.

Jason-299789 SSC-Insane Points: 21601 More actions · Answer 14

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