Select based upon Multiple criteria

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

General SQL Server Forums

New to SQL Server Programming

Select based upon Multiple criteria

Author

Topic

abuhassan

105 Posts

Posted - 2006-09-13 : 05:57:09

Hi

I would like to get records from a table and present a result set based upon the search fields

the search fields could be any of the following: PNo, Year, JNo, C1No6, C2No3, C3No3, C4No3,

they could enter any combination of these however if they dont enter any of the above then the search should not retrieve any thing. the table colunms are listed below and asample data set is also shown below.

Currently the only way i think it can be done is by writing multiple queries with different queries to be executed based upon the search field that have been filled? can it be done in a stored prcedure? and can it be done using non-dynamic sql?

Name, PNo, Year, JNo, C1No6, C2No3, C3No3, C4No3, RefImage

adam, 01, 1999, 099, 3yh333, 888, 989, 999, ref1999099.jpg
Brian, 01, 2005, 029, 3yh323, 828, 929, 929, ref1929099.jpg
sid, 04, 1989, 039, 3yh343, 838, 939, 939, ref1993399.jpg
jack, 06, 1996, 069, 3yh633, 868, 969, 969, ref1669099.jpg

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-09-13 : 06:01:30

No Dynamic SQL required

You can do something like this.


select *
from   table t
where  t.PNo  = coalesce(@PNo,  t.PNo)
and    t.Year = coalesce(@Year, t.Year)
and    t.JNo  = coalesce(@JNo,  t.JNo)
. . .

quote:
if they dont enter any of the above then the search should not retrieve any thing

just add a if condition


if PNo is not null and @JNo is not null and . . .
begin
    < the select query here> 
end

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-09-13 : 06:06:18

You can create SP with optional parameters like this:

Create Procedure usp_GetData
(
	@PNo int = NULL, 
	@Year int = NULL, 
	@JNo int = NULL, 
	@C1No6 varchar(30) = NULL, 
	@C2No3 varchar(30) = NULL, 
	@C3No3 varchar(30) = NULL, 
	@C4No3 varchar(30) = NULL
)
as
begin

	select * from Tbl
	Where 1 =	(case
				when @PNo is not Null then
					case
						when PNO = @PNo then 1
						else 0
					end
				else 0
			End) OR
		1 =	(case
				when @Year is not Null then
					case
						when Year = @Year then 1
						else 0
					end
				else 0
			End) OR
		1 =	(case
				when @JNo is not Null then
					case
						when JNo = @JNo then 1
						else 0
					end
				else 0
			End) OR
		1 =	(case
				when @C1No6 is not Null then
					case
						when C1No6 = @C1No6 then 1
						else 0
					end
				else 0
			End) OR
		1 =	(case
				when @C2No3 is not Null then
					case
						when C2No3 = @C2No3 then 1
						else 0
					end
				else 0
			End) OR
		1 =	(case
				when @C3No3 is not Null then
					case
						when C3No3 = @C3No3 then 1
						else 0
					end
				else 0
			End) OR
		1 =	(case
				when @C4No3 is not Null then
					case
						when C4No3 = @C4No3 then 1
						else 0
					end
				else 0
			End)
End

Harsh Athalye
India.
"Nothing is Impossible"

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-09-13 : 06:08:39

Damn!!!

Why I use CASE when there are other simple options like ISNULL and COALESCE for handling nulls

Harsh Athalye
India.
"Nothing is Impossible"

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-09-13 : 06:16:37

Somthing like this ?


select *
from   table t
where  ((t.PNo  = @PNo) or (@PNo Is Null))
and    ((t.Year = @Year) Or (@Year Is null))
and    ((t.JNo  = @JNo) OR (@JNo Is Null))
.....

Chirag

abuhassan

105 Posts

Posted - 2006-09-13 : 08:11:34

Hi how can i test it out im having problem

select *

from
(
select 'adam' as [Name], '01' as PNo, '1999' as Year,
'099' as JNo, '3yh333' as C1No6, '888' as C2No3, '989' as C3No3,
'999' as C4No3, 'ref1999099.jpg' as RefImage union all
select 'Brian', '01', '2005', '029', '3yh323', '828', '929', '929', 'ref1929099.jpg' union all
select 'sid', '04', '1989', '039', '3yh343', '838', '939', '939', 'ref1993399.jpg' union all
select 'jack', '06', '1996', '069', '3yh633', '868', '969', '969', 'ref1669099.jpg'

) t

abuhassan

105 Posts

Posted - 2006-09-13 : 08:20:01

so lets say if the user provides PNo and Year and C1No6 will that refine the results return i.e. return matching only those three criteria that were entered

LazyDragon
Starting Member

30 Posts

Posted - 2006-09-13 : 08:28:27

Have a look at

http://www.sommarskog.se/dyn-search.html

LazyDragon
T-SQL Programmer

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-09-13 : 08:38:54

Somthing like this ?


Declare @PNo varchar(10) , 
	@Year varchar(10), 
	@JNo Int , 
	@C1No6 varchar(30) , 
	@C2No3 varchar(30) , 
	@C3No3 varchar(30) , 
	@C4No3 varchar(30)

Select @PNo ='01' ,@Year = '1999', @C1No6 = '3yh333'

select *
from
(
	select 'adam' as [Name], '01' as PNo, '1999' as Year,
	'099' as JNo, '3yh333' as C1No6, '888' as C2No3, '989' as C3No3,
	'999' as C4No3, 'ref1999099.jpg' as RefImage union all
	select 'Brian', '01', '2005', '029', '3yh323', '828', '929', '929', 'ref1929099.jpg' union all
	select 'sid', '04', '1989', '039', '3yh343', '838', '939', '939', 'ref1993399.jpg' union all
	select 'jack', '06', '1996', '069', '3yh633', '868', '969', '969', 'ref1669099.jpg'
) t

Where 
       ((t.PNo  = @PNo) or (@PNo Is Null))
and    ((t.Year = @Year) Or (@Year Is null))
and    ((t.JNo  = @JNo) OR (@JNo Is Null))
and    ((t.C1No6  = @C1No6) OR (@C1No6 Is Null))
And    ((t.C2No3  = @C2No3) OR (@C2No3 Is Null))
And    ((t.C3No3  = @C3No3) OR (@C3No3 Is Null))
And    ((t.C4No3  = @C4No3) OR (@C4No3 Is Null))

Chirag

abuhassan

105 Posts

Posted - 2006-09-13 : 08:49:34

Hi

how can i make the PNo and Year as a mandatory if they are not provided then give no results?

abuhassan

105 Posts

Posted - 2006-09-13 : 08:52:40

had a look at

Have a look at

http://www.sommarskog.se/dyn-search.html

LazyDragon
T-SQL Programmer

I cant figure out what i need it looks too advanced for me to understand exactly whats going on and the difference in the approaches ...

Lazy dragon can you help get an easy example?

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-09-13 : 08:57:05

If @PNO and @YEar are not provided then it will return no output.


Declare @PNo varchar(10) , 
	@Year varchar(10), 
	@JNo Int , 
	@C1No6 varchar(30) , 
	@C2No3 varchar(30) , 
	@C3No3 varchar(30) , 
	@C4No3 varchar(30)

Select @PNo ='01' , @C1No6 = '3yh333'

select *
from
(
	select 'adam' as [Name], '01' as PNo, '1999' as Year,
	'099' as JNo, '3yh333' as C1No6, '888' as C2No3, '989' as C3No3,
	'999' as C4No3, 'ref1999099.jpg' as RefImage union all
	select 'Brian', '01', '2005', '029', '3yh323', '828', '929', '929', 'ref1929099.jpg' union all
	select 'sid', '04', '1989', '039', '3yh343', '838', '939', '939', 'ref1993399.jpg' union all
	select 'jack', '06', '1996', '069', '3yh633', '868', '969', '969', 'ref1669099.jpg'
) t

Where 
       ((t.PNo  = @PNo) )
and    ((t.Year = @Year))
and    ((t.JNo  = @JNo) OR (@JNo Is Null))
and    ((t.C1No6  = @C1No6) OR (@C1No6 Is Null))
And    ((t.C2No3  = @C2No3) OR (@C2No3 Is Null))
And    ((t.C3No3  = @C3No3) OR (@C3No3 Is Null))
And    ((t.C4No3  = @C4No3) OR (@C4No3 Is Null))

Chirag

abuhassan

105 Posts

Posted - 2006-09-13 : 09:06:18

Thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources