Sounds nicely politically correct, however, what do you do when the users themselves don't know quite what they want? I run into this a lot - they have a vague...

jerry-621596 (8/9/2012)

---

If you are using Acess for the application side, why don't you pull the original dataset into Access and work to refine that instead of trying to track a...

Yes, it will almost always be a scan in these cases - that's the problem. I'm handling the edge cases...

Lowell (8/9/2012)

---

what about going ahead and using either a permenant or a global temp table, but adding an extra column with some identifier to the user or session, ie the...

SomewhereSomehow (8/9/2012)

---

Now I understand what are you trying to do, and my idea with temp table (even it is technically possible, and even if you'll find the way to issue...

Gazareth (8/9/2012)

---

You'd need some way in Access to execute T-SQL (or equivalent) rather than calling a proc.

I don't use Access so I've no idea if it has that ability, sorry.

e.g....

Gazareth (8/9/2012)

---

No, if you create a local temp table outside a proc, it isn't destroyed until the creating session disconnects (as it doesn't go out of scope).

So this table could...

It is, but also somewhat frustrating. It seems odd to me that there isn't an automatic session-specific, private temp table mechanism. It doesn't seem like...

You mean create a permanent table that would stay in the database? I could,...

Gazareth (8/9/2012)

---

Sounds like an interesting problem!

I might be oversimplifying this, but is the purpose of each step just to refine the selection criteria (i.e. your WHERE clause)?

If so, would it...

Unfortunately, I don't have all the actions available at once. I need to create the table and return a count to...

myheadhurts (7/30/2012)

---

I wouldn't start splitting out your functionality into separate sprocs in the hope it will improve performance. Spend the time to understand why the slow ones are...

I just tested my original version (with all parameters and the AND/OR constructs) with the RECOMPILE instruction and it uses the indexes, just as when I coded the individual statements...

Thanks, that also helped clear up some confusion about injection...

I've just assembled this:

alter PROCEDURE [srchAkc].spAkcesIDSrch

-- Add the parameters for the stored procedure here

@AkcesitPred varchar(2) = '',

@Akcesit int = 0,

@Rok smallint = 0

AS

BEGIN

SET NOCOUNT ON

-- Direction variable

declare @tw tinyint

set...