Parameterized Query

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

Guest

I'm trying to use a SqlParameter for the IN operator in a T-SQL statement:

myCommand.CommandText = "SELECT CustomerID, CustomerName FROM Customers
WHERE AccountNumber IN (@accountNumbers)";

The problem I am having is that I can't figure out what to assign
@accountNumbers to:

SqlParameter param = myCommand.Parameters.Add("@accountNumbers",
SqlDbType.VarChar);
param.Value = ???

Any advice???


Thanks,
Nate
 
Nate said:
I'm trying to use a SqlParameter for the IN operator in a T-SQL statement:

myCommand.CommandText = "SELECT CustomerID, CustomerName FROM Customers
WHERE AccountNumber IN (@accountNumbers)";

The problem I am having is that I can't figure out what to assign
@accountNumbers to:

SqlParameter param = myCommand.Parameters.Add("@accountNumbers",
SqlDbType.VarChar);
param.Value = ???

Any advice???
Click to expand...

You cannot do that. You should build you command string listing
all individual account numbers as separate parameters.
 
This is one of the few places paramaterized queries fall down, you can't
parse a list of values in a parameter.

The easiest solution is if you can change it to work using min and max
bounds.

The only other solutions I have seen involve using a stored procedure
that splits a comma delimited varchar.

If you're using IN and parsing in a parameter you can't be parsing in
that many? If so could you not just query each one individualy as this
is the easiest way.
 
That cannot be done. You can put together a list as XML and run an OPEN XML
(better in a sproc, as it is hard to debug dynamically. In a sproc, you can
also pull apart a separated list. But, you cannot have an in. You will have
to build the entire string yourself prior to sending it. And, yes, there is
a perf penalty, as you will have to run the execution plan every time.

--
Gregory A. Beamer

*************************************************
Think Outside the Box!
*************************************************
 
That cannot be done. You can put together a list as XML and run an OPEN XML
(better in a sproc, as it is hard to debug dynamically. In a sproc, you can
also pull apart a separated list. But, you cannot have an in. You will have
to build the entire string yourself prior to sending it. And, yes, there is
a perf penalty, as you will have to run the execution plan every time.
Click to expand...

As opposed to this you could build the sql query on the client machine.
As you are working with numbers, as long as your code has ensured they
are numbers (i.e. if they were strings run something like Integer.parse
on them) you could just append them to the query.

Otherwise, if you were using strings you could look at using dynamic sql
with parameters (instead of directly in-lining the strings). This is all
assuming that your client machine was the one executing that sql
statement on the server, as opposed to that statement being part of a
stored procedure.
 
As opposed to this you could build the sql query on the client machine.
As you are working with numbers, as long as your code has ensured they
are numbers (i.e. if they were strings run something like Integer.parse
on them) you could just append them to the query.

Otherwise, if you were using strings you could look at using dynamic sql
with parameters (instead of directly in-lining the strings). This is all
assuming that your client machine was the one executing that sql
statement on the server, as opposed to that statement being part of a
stored procedure.
Click to expand...

I did this just a while back, but as everyone has said, you will have to use
dynamic SQL to do it.

Construct the beginning and end of the SQL string as separate strings and build
the list of items with a loop or something like that and place the list in the
middle between the two other strings. Be careful about how many list items you
add. I found real fast that Oracle 8i (yes I know we're behind the curve) won't
accept more than 1000 elements in an IN statement. Don't ask why we tried to
use 1000 items. It was an MBA managementization thingatizer.

Good luck with your project,

Otis Mukinfus
http://www.arltex.com
http://www.tomchilders.com
 
The query Nate is asking for is something like this:

SELECT * FROM Table1
WHERE Col1 IN (1,3,4,5)

The only point for a parameter, client side, is the where clause and you
cannot parameterize multiple values. You can certainly do something that
pounds the items together and sends a single string, but you will not
benefit from execution cache plans on a server like SQL Server. Of course,
if this is Access, all bets are off. :-)

XML is a great option, if the sql in the sproc is fully qualified. It would
still be wise to profile and ensure you are hitting a cached execution plan
as there are many things in SQL Server sprocs that can invalidate the cache
option.

Yes, you could move to the client, but you still lose the benefit on the
server side, which is going to be the bottleneck in this type of situation.
Assembling the query, client or server side, will take very few cycles. It
is actually pulling the data that will cost the most.

Unfortunately, there are no real easy answers to a IN or NOT IN type of
query.

--
Gregory A. Beamer

*************************************************
Think Outside the Box!
*************************************************
 
Just curious

if the accountNumbers can be derived from the db, then simply use

SELECT CustomerID, CustomerName FROM Customers WHERE AccountNumber IN (
SELECT accountNumber FROM tblName WHERE ... )
 
Back
Top