Best way to tackle dynamic searches

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)

Best way to tackle dynamic searches

Author

Topic

Bex
Aged Yak Warrior

580 Posts

Posted - 2005-04-08 : 10:50:02

Afternoon all

I have to write a sproc to filter data depending on search criteria provided by the user. The search criteria are listed below:

Account Number
Customer Name
Date
Invoice Number
Telephone Number

They may provide one or more criteria, and perhaps only half of one (for example, 123....., for the AccountNumber). However, it is more than likely the account number will be the main search, eventually drilled down using the rest of the fields.

I have no idea how to approach this in a stored procedure or procedures or any other method for that matter. Any recommendations/advice/links to articles/information (other than BOL) would be greatly appreciated!

I know this is very vague, but I am looking for the general approach rather than specifics, if that makes any sense! Thanks

Hearty head pats

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-04-08 : 11:20:45

sproc for sure.

select ...
from table1 inner join table2 on ....
where accNumber like '%' + accNumber + '%' and
date = @date and
...

i guess that's the general approach

Go with the flow & have fun! Else fight the flow

Bex
Aged Yak Warrior

580 Posts

Posted - 2005-04-08 : 11:32:24

Hey Spirit

Now that I know a sproc is the way forward, how do I structure the query. Would I write a different query for each possible combination of parameters that maybe supplied, or do I somehow write a single query that will simply run on the values supplied. In the example you provided, how would this run if, say, if I included all the parameters in the query, and only one (say the @accNum) was provided?

select ...
from table1 inner join table2 on ....
where accNumber like @accNumber + '%' and
date = @date and .......

Anyway, I have to go now! But thanks for your reply and I hope to pick this up on Monday! Have a great weekend!!! I am having a BBQ so am hoping the weather will improve!

Speak on monday!!

Hearty head pats

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-04-08 : 11:55:49

select ...
where (accNumber like @accNumber + '%' or @accNumber is null) and (date = @date or @date is null) and ....

of course the parameters have to have the default values null and those not needed shouldn't be passed into a sproc.

Go with the flow & have fun! Else fight the flow

Bex
Aged Yak Warrior

580 Posts

Posted - 2005-04-11 : 03:40:23

Good morning!

Cheers for the advice Spirit! Did you have a good weekend?

You mentioned setting the default value of the parameters as null? Is the format similar to the CREATE TABLE statement:


CREATE PROCEDURE ......
(
@param1 VARCHAR(12) NULL,
@param2 INT NULL,
@param3 VARCHAR(15) NULL,
ETC.....
)
AS....

Thanks

Hearty head pats

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-04-11 : 05:13:15

yup.
had a good weekend.
it rained all the time so i've done absolutly nothing.

Go with the flow & have fun! Else fight the flow

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources