Problem in where clause - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Problem in where clause

Author

Topic

anujkrathi
Starting Member

9 Posts

Posted - 2010-09-25 : 04:19:33

Hi Experts,
I am facing a problem in my query.

My table contain records like below:

Column1 Column2 Comumn3
1 1 Value1
1 NULL Value2
NULL NULL Value3

I have two variables @val1 and @val2 & values of these variables can not be NULL.

Now, I have 3 situation where I want to fetch the result.
@val1 = somevalue @val2 = @somevalue
1. when Column1 = @val1 and Column2 = @val2 fetch first row
if above condition fails then check for second one

2. when Column1 = @val1 and Column2 is NULL fetch second row
if above condition fails then check for third one

2. when Column1 is NULL and Column2 is NULL fetch third row

& fetch the value of COLUMN3 as the result.

SELECT * FROM Table1
where ((Column1 = @val1 AND Column2 = @val2)
OR (Column1 = @val1 AND Column2 IS NULL )
OR (Column1 IS NULL AND Column2 IS NULL))

but in above case, if condition is true, then it will fetch all 3 rows, if second condition is true, it will fetch 2nd & 3rd row.

I don't want to use if else condition because my table has thousands of rows & then I will have to search my table 3 times if I check 3 conditions individually.

Is there any condition with the help of CASE. I tried but results are not coming correct.

please suggest me some solution.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-09-25 : 05:23:33

[code]DECLARE @Sample TABLE
(
Column1 INT,
Column2 INT,
Column3 VARCHAR(20)
)

INSERT @Sample
VALUES (1, 1, 'Value1'),
(1, NULL, 'Value2'),
(NULL, NULL, 'Value3')

DECLARE @Val1 INT = 1,
@Val2 INT = 2

SELECT TOP(1) WITH TIES
*
FROM @Sample
ORDER BY CASE
WHEN Column1 = @Val1 AND Column2 = @Val2 THEN 0
WHEN Column1 = @Val1 AND Column2 IS NULL THEN 1
WHEN Column1 IS NULL AND Column2 IS NULL THEN 2
ELSE 3
END[/code]

N 56°04'39.26"
E 12°55'05.63"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources