Search Engines!!!!! - 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

Other Forums

MS Access

Search Engines!!!!!

Author

Topic

PETE314
Starting Member

37 Posts

Posted - 2004-11-15 : 14:39:39

I have a Search Engine that is being converted from .mdb and a temptableResultset(created from VBA code) to an adp and stored procedure (as this may end up being the best method...if not please advise). The book I have been using has been very helpful. However in their little section concerning a Search Engine they, for the most part, have only 2 parameters. This gives us a possibility of 4 different Scenarios...

1) A=Null B=Null
2) A=Not Null B=Null
3) A=Null B=Not Null
4) A=Not Null B=Not Null

And so they in their stored procedure example had built a set of select statements for each scenario.

In my .mdb version I created an INSERT statement in code and then ran it to enter the data into a temp table...and the form would be bound to the temp table. So basically I had an IF statement in VBA for each parameter to create the WHERE statement.

My quandary is this....I have 6 parameters...giving me 64 scenarios(I will not list them here LOL) I would prefer not to create a set of SQL Statements for each and every scenario. Especially when the only difference is the WHERE statement. I would also like to avoid creating a stored procedure in code every time since I am going to have several users at the same time. And I have had very little luck getting forms to be bound to VBA recordsets(which should be simple.

)

There seems to be a couple ways of accomplishing the feat at hand...what are the best methods??? Is there a way to build just the WHERE statement depending on which parameters are filled in writing the stored procedure???

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-11-15 : 15:13:32

Where
(columnData = @variableA or @variableA is null)
And
(columnData = @variableB or @variableB is null)
...

or

Where
(column1Data = @variableA or @variableA is null)
And
(column2Data = @variableB or @variableB is null)
...

Which ever applies

Corey

X002548
Not Just a Number

15586 Posts

Posted - 2004-11-15 : 16:28:26

a scanning we will go, a scanning we will go, hi ho...oh never mind

How much data are yo talking about?

Brett

8-)

PETE314
Starting Member

37 Posts

Posted - 2004-11-16 : 07:55:19

quote:
Originally posted by X002548

a scanning we will go, a scanning we will go, hi ho...oh never mind

How much data are yo talking about?

Brett

8-)

If every parameter is null the resultset we are talking about is over 100,000 records(after several years worth of data entered). But when the parameters are applied(99.99% of the time), the resultset will usually be less than 25 but sometimes a little more...I plan to grab the top 25 (or maybe 50, depending upon testing) records and then give them buttons for the next 25 and previous 25. My SQL statement (except for the WHERE clause which is variable depending upon what parameters have been filled) is all set.

PETE314
Starting Member

37 Posts

Posted - 2004-11-16 : 08:18:25

quote:
Originally posted by Seventhnight

Where
(columnData = @variableA or @variableA is null)
And
(columnData = @variableB or @variableB is null)
...

or

Where
(column1Data = @variableA or @variableA is null)
And
(column2Data = @variableB or @variableB is null)
...

Which ever applies

Corey

Thanks for the statement....however I am a little confused...maybe because I am not 100% up on my T-SQL.....but wouldn't that set the parameter for the recordset as "null". When I would want that to be "%" If they do not use the parameter I want all records to come back.

For instance my vba code created an INSERT INTO procedure(since it was an .mdb and I was using temp tables) and my code would assign variables depending upon the values in the parameter fields. Once the main portion of the SQL statement was built I then proceded to build the Where statement with a series of If statements.

OSE = " WHERE (((dbo_tblOrderInformation.blnCanceled)=" & 0 & ")) AND"

'programatically create parameters
If Addy <> "noaddy" Then 'noaddy is the value set to the variable if the value of the parameter field is null
OSE = OSE & " ((dbo_tblPropertyDetails.strPropertyAddress) Like '%" & Addy & "%') AND"
End If
If Name <> "noname" Then
OSE = OSE & " (dbo_tblPropertyOwnerLastName.strPropOwnLastName) Like '%" & Name & "%' AND"
End If

So as you can see if the parameter field is null it doesn't even become a part of the WHERE Statement. I then trim off the final "AND" and voila I have my where statement.

So as I said I may not be up 100% on my T-SQL but it just seems that your statement sets the parameter in the where clause to null bringing up only those records in the resultset where that field is null....am I wrong in my assumption????? Thanks for any info...

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-11-16 : 09:41:11

Examine the 'where' clause from the boolean (true/false) stand point. A record is shown if the where clause evaluates to (true)

Where
(columnData = @variableA or @variableA is null)
And
(columnData = @variableB or @variableB is null)

Assume
- @variableA is not null
- @variableA is equal to columnData
- @variableB is null

The where clause becomes:

Where
(true or false)
And
(false or true)

(true Or false) = true
(false Or true) = true
true And true = true

Thus the record should be shown as the where clause evaluted to true

Now Consider
- @variableA is null
- @variableB is not null
- @variableB is not equal to columnData

The where clause becomes:

Where
(false or true)
And
(false or false)

(false Or true) = true
(false Or false) = false
true And false = false

Thus the record should not be shown as the where clause evaluted to false

Better?

Corey

PETE314
Starting Member

37 Posts

Posted - 2004-11-16 : 10:27:33

Ahhhhhh, I see, said the blind man as he picked up his hammer and saw....

Thanks for the explanation.

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-11-16 : 10:34:16

quote:
Originally posted by PETE314

Ahhhhhh, I see, said the blind man as he picked up his hammer and saw....

Thanks for the explanation.

I've always enjoyed that quote, but so few people have heard it (at least of the people I know)

Glad it was clear enough, I often have a problem with that

Corey

PETE314
Starting Member

37 Posts

Posted - 2004-11-22 : 09:16:10

Thanks alot for the help...the engine works great....although it might be a touch slower(but "well" within acceptable speeds)....I believe this only happens when more than one parameter is used. When just one parameter is used it is just as fast or faster actually. And this is the method used most often. I also got to cut out more than 2 pages of code from the form. I have a couple other engines that will recieve much of the same changes. Great fix...thanks!!!!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources