|
dev
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Parameterized QueryI'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 wrote:
> I'm trying to use a SqlParameter for the IN operator in a T-SQL statement: You cannot do that. You should build you command string listing> > 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??? 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. On Tue, 27 Jun 2006 10:29:01 -0700, Nate <N***@discussions.microsoft.com> wrote: Show quote > >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 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. -- Show quoteGregory A. Beamer ************************************************* Think Outside the Box! ************************************************* "Nate" <N***@discussions.microsoft.com> wrote in message news:7C675579-B5CF-498B-9515-0FE93F5EBD6E@microsoft.com... > > 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 On Tue, 27 Jun 2006 17:22:16 -0500, "Cowboy \(Gregory A. Beamer\)"
<NoSpamMgbworld@comcast.netNoSpamM> wrote: >That cannot be done. You can put together a list as XML and run an OPEN XML As opposed to this you could build the sql query on the client machine.>(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. 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. On Wed, 28 Jun 2006 00:21:44 +0100, Chris Chilvers <kee***@dynafus.com> wrote:
Show quote >On Tue, 27 Jun 2006 17:22:16 -0500, "Cowboy \(Gregory A. Beamer\)" I did this just a while back, but as everyone has said, you will have to use><NoSpamMgbworld@comcast.netNoSpamM> wrote: > >>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. > >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. 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. -- Show quoteGregory A. Beamer ************************************************* Think Outside the Box! ************************************************* "Chris Chilvers" <kee***@dynafus.com> wrote in message news:94f3a2l5dehmpcghgbl3pko0j9al0p3d6j@4ax.com... > On Tue, 27 Jun 2006 17:22:16 -0500, "Cowboy \(Gregory A. Beamer\)" > <NoSpamMgbworld@comcast.netNoSpamM> wrote: > >>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. > > 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. 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 ... )  |
|||||||||||||||||||||||
Other interesting topics