Board index » delphi » Query Parameter Question

Query Parameter Question

I have a Tquery hooked to MSSQL7 DB
WHERE Table.State In(:STATES)

where :STATES is the input parameter for the query.
My problem is I am not sure how to format the string for the parameter so
that the query functions correctly?


Re:Query Parameter Question

Unfortunately the IN keyword is not supported (via a parameter type).  You
will have to build the SQL IN parameter list yourself.  I do this a lot and
I can send you a function that makes this easy.

Re:Query Parameter Question

Hi Phil
Im afraid you will have to change your approach, you can't send array values
as params and that is effectively what you are asking it to do.

What I tend to do in this situation is create a temporary table on the
server (see CREATE TABLE #...), populate it with the values I want, in your
case all the States that you are putting into the string, and then you WHERE
condition could be changed to...

WHERE Table.State In(SELECT State FROM #tmpStates)

The single # on the table name means it is private to that session, other
users can have there own temp table without any conflicts. Don't forget to
drop the temp table afterwards.

The creating and dropping of temporary tables is very quick, but it would be
better if you could take it a step further back to when you are building
your list of states in the first place. If the list is built from an SQL
statement you could put that in the WHERE clause of the sub SELECT. If its
based on the user ticking a number of options then the first option is the
way to go.

Hope this helps.

John Hair
Designer Systems Ltd

Other Threads