create procedure your_procedure (@lcAssets nvarchar(max))
as
begin
declare @ta table(assets nvarchar(100))
Declare @x xml = cast('<Rows><Row>'+replace(@lcAssets,',','</Row><Row>') + '</Row></Rows>' as xml) -- Convert input parameter to xml form
insert into @ta
select a.b.value('.','nvarchar(100)')
from @x.nodes('Rows/Row') a(b) ---- generate tabular for through xquery
--select * from @ta
SELECT Assets.Number,AssetsHistory.dtMoved
FROM @ta t join Assets on ----input parameter table in inner join
t.assets=Assets.Number
LEFT OUTER JOIN AssetsHistory ON
AssetsHistory.keyAssets = Assets.keyAssets
end
go
exec your_procedure 'A101,A102,A103'
DECLARE @lcAssets CHAR(400)
SET @lcAssets = 'A101,A102,A103';
WITH CTESplit
AS
(
SELECT
0 AS Pos,
1 AS PosStart,
CHARINDEX(',',@lcAssets + ',') AS PosEnd
UNION ALL
SELECT
Pos + 1 AS Pos,
PosEnd + 1 AS PosStart,
CHARINDEX(',', @lcAssets + ',',PosEnd + 1 ) AS PosEnd
FROM CTESplit
WHERE CHARINDEX(',', @lcAssets + ',',PosEnd + 1 ) > 1
),
CTEValue
AS
(
SELECT
SUBSTRING(@lcAssets,PosStart,PosEnd - PosStart) Value
FROM CTESplit
)
SELECT Assets.Number,AssetsHistory.dtMoved
FROM Assets LEFT OUTER JOIN AssetsHistory ON
AssetsHistory.keyAssets = Assets.keyAssets
INNER JOIN CTEValue ON
CTEValue.Value = Assets.Number
So if you do it as:
Open in new window
That should get you results.