January 29, 2013 at 10:50 pm
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. 🙂 🙂 🙂
January 29, 2013 at 11:10 pm
Please share your answers..... need help.....!!!!!!!
January 29, 2013 at 11:12 pm
January 30, 2013 at 5:47 am
use below code.
declare @TBLCOLOR TABLE
(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
DECLARE @COLOR VARCHAR(20)=NULL
SELECT * FROM @TBLCOLOR
--WHERE ISNULL(COLOR,'U') IN(CASE WHEN @COLOR IS NULL THEN ISNULL(COLOR,'U') ELSE @COLOR END)
WHERE COALESCE(COLOR,'U') IN(COALESCE(@COLOR,COLOR,'U'))
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply