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.

Thanks,

David