March 8, 2016 at 1:09 pm
I use code below to select customer name. How many customer's name can be stored in ()?
It may be over 200, 500.
select * from order where CustomerName in ('A', 'B',....)
March 8, 2016 at 1:46 pm
There is no hard limit on the number of items you can specify in the IN list, but there's some considerations regardin the size of the batch, which could increase SQL Server compilation times and exceed the maximum batch size for the connection.
How many items are you planning to pass in that list? Would it be possible to store them in a temporary table or a TVP instead?
-- Gianluca Sartori
March 8, 2016 at 1:54 pm
Just to illustrate...
USE [AdventureWorks2012]
--Using IN
SELECT
*
FROM
[Production].[ProductInventory]
WHERE
LocationID IN (1, 6, 50)
--Using a Join
DECLARE @test-2 TABLE (LocationID INT)
INSERT INTO @test-2
VALUES (1), (6), (50)
SELECT
p.*
FROM
[Production].[ProductInventory] p
JOIN @test-2 t ON t.LocationID = p.LocationID
March 9, 2016 at 7:44 am
In fact, this question is coming from a vb.net project.
All items in list is coming from one array list which user selects from a Gridview select column.
Once user clicks select all, list items will become a big number.(I code to store items in an array list)
I knew that I can code to insert selected items into a table in server and then use inner join.
But, I am searching a another way to complete it in application.
March 9, 2016 at 8:14 am
So, I think that I need to create a table to store the item user selected and then use inner join to complete job in application.
March 9, 2016 at 12:55 pm
I could write a lengthy reply, but I'll save some time and simply post a link to a very excellent and very detailed writeup on various ways of passing long lists from client code to SQL Server: http://www.sommarskog.se/arrays-in-sql.html
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply