Sergey,

As always, thanks for your helpful suggestions. I tried using a table variable yesterday and I couldn't get my JOIN syntax but I figured out today. I figured you wouldn't suggest anything uncodable. My problem was that I was trying

DECLARE @customerIDTable TABLE (customerID INT)

INSERT @customerIDTable VALUES (1)
INSERT @customerIDTable VALUES (2)
INSERT @customerIDTable VALUES (3)

SELECT	@customerIDTable.customerID, a.*
FROM	@customerIDTable
	INNER JOIN Address a
		ON a.customerID = @customerIDTable.customerID

which doesn't work, but

SELECT	c.customerID, a.*
FROM	Address a
	INNER JOIN @customerIDTable c
		ON a.icustid = c.customerID

works. A bug in the MS SQL parser?

I was disappointed that I couldn't get a JOIN on a TABLE variable to work because I find so few instances in which I can get them to work. Does anyone know if you will be able to INSERT INTO them in MS SQL 2005? What are the advantages of using them over a temp table, anyway, besides not worrying about not dropping them?

David

>Hi David,
>
>You may consider a couple more options:
>- Using Table variables instead of temp tables
>- Passing the list of PKs in XML format and use OPENXML() in queries
>
>Only test can tell which options will be most efficient.
>
>
>>Our database is far away from some of its clients, so we are trying to pull as much in a single query as we can. I am writing a stored procedure that takes a comma delimited string of PKs and then uses them to find other records, a total of 15 SELECTs. There could be up to around 10 PKs or so in the string, but the number needs to be variable for flexibility.
>>
>>I wanted to know how to write the most efficient SP. The two options that occurred to me are:

1. EXECUTE ('SELECT street, city, state
>>             FROM   Address
>>             WHERE  customerID IN (' + @pkString + ')')
>>
>>   EXECUTE ('SELECT orderID, amount
>>             FROM   Orders
>>             WHERE  customerID IN (' + @pkString + ')')
>>
>>   -- Additional SELECTs...
>>
>>2. CREATE TABLE #customerIDTable (customerID INT)
>>
>>   DECLARE @StartIndex INT
>>   SET @StartIndex = 0
>>   DECLARE @CustomerID VARCHAR(10)
>>
>>   SET @CommaIndex = CHARINDEX(',', @pkString, @StartIndex)
>>		
>>   WHILE @CommaIndex > 0
>>   BEGIN
>>      SET @CustomerID =
>>         LTRIM(SUBSTRING(@pkString, @StartIndex, @CommaIndex - @StartIndex))
>>			
>>      INSERT #customerIDTable VALUES (@CustomerID)
>>			
>>      SET @StartIndex = @CommaIndex + 1
>>      SET @CommaIndex = CHARINDEX(',', @pkString, @StartIndex)
>>   END
>>
>>   -- This either gets the last pk in @pkString or the single
>>   -- customerID in @pkString
>>   SET @WarehouseID =
>>      LTRIM(SUBSTRING(@Warehouses, @StartIndex, @WarehousesLength - @StartIndex + 1))
>>   INSERT #WarehouseIDs VALUES (@WarehouseID)
>>
>>   SELECT add.street, add.city, add.state
>>   FROM   Address add
>>          INNER JOIN #customerIDTable cidt
>>              ON add.customerID = cidt.customerID
>>
>>   SELECT ord.orderID, ord.amount
>>   FROM   Orders ord
>>          INNER JOIN #customerIDTable cidt
>>              ON ord.customerID = cidt.customerID
>>
>>   -- Additional SELECTs...
>>
>>   DROP TABLE #customerIDTable

>>Is there a more efficient way? I doubt that the second option would pay off unless I was doing quite a few SELECTs. All the tables that I am JOINing them with are pretty big.
>>