Posted by Erland Sommarskog on 12/19/06 22:51

(msrviking@gmail.com) writes:
> I need results based on below criteria,
>
> 1.
> Criteria - B.Col2 = 100
> Resultset
> A.Col1 D.Col1
> 1 Value1
> 2 Value2
>
> 2.
> Criteria - B.Col2 =""
> A.Col1 D.Col1
> 1 Value1
> 2 Value2
> 3 NULL
>
> 3.
> Criteria - B.Col2 =200
> Empty resultset
>
> Here is the query I tried, but looks its not working. Probably there is
> a better way to do this.

Thanks for posting the CREATE TABLE and INSERT statements. That makes
it easy to test. Here is a solution that gives the desired result. Since
B.Col2 is numeric, it cannot be a string value, so I am assuming NULL
for this case.

create table #tab1 (a1 int, a2 nvarchar(20))
create table #tab2 (b1 int, b2 int)
create table #tab3 (c1 int, c2 int)
create table #tab4 (d1 int, d2 nvarchar(20))

insert into #tab1 values (1, 'nm1')
insert into #tab1 values (2, 'nm2')
insert into #tab1 values (3, 'nm3')

insert into #tab2 values (10, 100)
insert into #tab2 values (20, 200)

insert into #tab3 values (1, 10)
insert into #tab3 values (2, 10)

insert into #tab4 values (1, 'value1')
insert into #tab4 values (2, 'value2')
go
create procedure #testie @val int AS
select a.a1, d.d2
from #tab1 a
left join #tab4 d ON a.a1 = d.d1
WHERE @val IS NULL OR
EXISTS (SELECT *
FROM #tab3 c
JOIN #tab2 b ON c.c2 = b.b1
WHERE c.c1 = a.a1
AND b.b2 = @val)
go
EXEC #testie 100
EXEC #testie NULL
EXEC #testie 200
go
drop table #tab1, #tab2, #tab3, #tab4
drop proc #testie




--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]