Searching with various optional parameters

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 2000 Forums

SQL Server Development (2000)

Searching with various optional parameters

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-08-02 : 09:31:45

Monica writes "Hi!

I am developing a search screen in VB with many search options (Customer, Customer Number, Invoice Number, Order Number, Work Order Number, etc) and would like to give the user the ability to enter all, some or none of the data in the text boxes and search accordingly. If all items are blank, the results will show all of the information for that customer.

I will pass the values of those text boxes to a stored procedure on the SQL server (SQL server 2000). My problem is I do not know how to exclude the parameters that they leave blank and include only those that they entered criteria for.

This one has me stumped. I really hope you can answer this for me.

Thank you in advance for your help.

Monica"

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2002-08-02 : 09:57:23

you could do something like the following....

set lowvalue1,2,3 = 0
set highvalue1,2,3 = 9999999999999

if not isnull(inparm1) then
lowvalue1 = inparm1, highvalue1 = inparm1
endif

etc....

select * from customer
where (name >= lowvalue1
and name <= highvalue1)
or (code >= lowvalue2
and code <= highvalue2)
or (address >= lowvalue3
and address <= highvalue3)

it's one option!....

r937
Posting Yak Master

112 Posts

Posted - 2002-08-02 : 20:44:37

sorry if i don't have the exact syntax, i don't do vb

basically you have to be able to test in the stored proc for a variable that's passed in as "unselected" -- perhaps this is a zero value, or an empty string, or something

in the stored proc, write your query like this pseudocode:

select foo from bar
where 1=1
%IF param1 not unselected%
and column1 = param1
%ENDIF%
%IF param2 not unselected%
and column2 = param2
%ENDIF%
etc.

each time a parameter value is not unselected, you generate another AND condition, since the search should be more restrictive with additional parameters

if none of the parameters were passed in with a selected value, the WHERE 1=1 will still be in effect, and will return all rows in the table

rudy
http://rudy.ca/

CMartin
Starting Member

13 Posts

Posted - 2002-08-03 : 00:25:52

What I understood is that you want to pass all the fields to the store procedure, even if they are blank, and then control the search inside it.
If that's the case here's one solution that I use when I need to use dynamical AND searches.

I assume that at least you will pass always the customer to the store procedure and you want to do an AND search with the other fields.


SELECT * FROM Customers
WHERE Customer = @Customer
AND 1 =
	CASE
		WHEN @CustNr IS NOT NULL AND CustNumber = @CustNr THEN 1
		WHEN @CustNr IS NULL THEN 1
	END
AND 1 =
	CASE
		WHEN @InvNr IS NOT NULL AND InvoiceNr = @InvNr THEN 1
		WHEN @InvNr IS NULL THEN 1
	END
AND 1 =
	CASE
		WHEN @OrdNr IS NOT NULL AND OrderNr = @OrdNr THEN 1
		WHEN @OrdNr IS NULL THEN 1
	END
...

This will search only by the fields that are not null.

Carlos

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources