Passing stored procedure parameter to SQL In values list

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,

In Microsoft Access, using the Northwind database as an example I would like
to use a stored procedure with parameters to do this:

SELECT EmployeeID
FROM Employees
WHERE EmployeeID In (1,2,3)

The best I have come up with from an big Internet search (with most examples
in SQL Server) is

PARAMETERS strList text(255);
SELECT EmployeeID
FROM Employees
WHERE EmployeeID In (' & @strList & ')

When running the query I input: 1,2,3
which doesn't work.

Can anyone help?

Cheers,
Tim.
 
Dear Tim:

The parameter you're trying to use will be interpreted as a single
text value, not as 3 numeric values.

There is a limited way of doing this, but it will not use indexing of
the table and may be somewhat inefficient. But it's simple to
implement and may work well within limited circumstances.

WHERE INSTR("," & strList & ",", "," & CStr(EmployeeID) & ",") > 0

This trick searches for ,1, in ,1,2,3,

By putting commas around the strings, it will not find 1 in 11,12,13

The user must enter the list of values in the parameter without
spaces! Other than that, for numeric values, it may work quite well.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


On Thu, 21 Oct 2004 10:59:01 -0700, "Tim Chilton" <Tim
 
That's an interesting trick Tom. That's why I love this forum, I'm always
picking up new ways to approach things.

Tim, You don't say what you are using this for, or whether you are familiar
with VBA, but another option is to construct the entire SQL string in VBA and
open the query from there.

-Ted Allen
 
Dear Ted:

It's a rather limited trick, and I've long ceased using it in
applications. It puts too much on the user. In fact, I never use
parameter queries any more. They're always based on controls. For
this one, I'd have probably used a multi-select list box.

So, I supply this with the understanding that is may work well if you
can discipline the users in how to use it. Don't type in too long a
string. Don't ever put spaces in the string. Don't forget any of the
commas. It's just too much to expect of MY end users.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
I agree that the use may be limited (I also tend to use list boxes and/or
checkboxes to set up query by form needs), but it is still interesting to see
creative approaches to things that generally broaden how we look at solving
problems.
 
Cheers for the help guys. I'm actually using the SQL on an ASP.NET Mapping
website. Users select features on the map and the unique IDs get passed to
their related table in an Access database and their details returned. I've
already done it by creating SQL in the ASP.NET pages but the stored
procedures would help keep things tidier and be easier to manage since I've
got a lot of queries that can be run from the map. Do you know if there are
any other efficient ways to organise lots of queries without having to
generate SQL dynamically?
 
Dear Ted:

I'll just take that as a compliment.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
Hi Tim,

Unfortunately I'm not really familiar with the process that you are talking
about, so I don't know that I can of too much help for that. You say that
they values are being passed to Access. Maybe it would be possible to write
the id's to a temp table and then have your various queries be joined to the
that table? (rather than trying to pass them into a query parameter).

Sorry I couldn't be more help, hopefully others may provide additional
suggestions. If not, you may want to try a new post with a little more
information of the process and what you are trying to accomplish, and ask for
general recommendations as to how to approach it.

-Ted
 
Certainly not!

It was intended as one. I hope it didn't come across differently.
Click to expand...

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
Back
Top