In order to parse an array passed in as a parameter in a stored procedure I use a stored procedure ripped from here: http://vyaskn.tripod.com/passing_arrays_to_stored_procedures.htm
To be more precise, it is method 5:
SELECT o.OrderID, CustomerID, EmployeeID, OrderDate
FROM dbo.Orders AS o
rID, number+1, CHARINDEX(',', OrderID, number+1)-number - 1))) AS OrderID
SELECT ',' + @OrderList + ',' AS OrderID
) AS InnerQuery
n.Number < LEN(InnerQuery.OrderID)
WHERE SUBSTRING(OrderID, number, 1) = ','
) as Derived
ON o.OrderID = Derived.OrderID
I use this sp to insert records in a table and instead of one column per insert (row) I need to do more.
For example, my input string would be something like:
However, I can't get my head around how to build this query. I have tried a cross join to multiple Numbers table but that generates a cartesian product (I cannot get a distinct-like result).
Is there any way around it?