Use a single SQL stored procedure parameter containing multiple expressions in a WHERE column_name IN (parameter) clause
Posted on 2012-12-26
The intention here is to allow users to search for information on a random quantity of assets via one call to a SQL stored procedure . For example, the SP might receive 'A101' or 'A101,A102,A103,...' as the parameter and need to return info on each asset identified.
In simple testing, the following statement returns the expected results:
DECLARE @lcAssets CHAR(400)
SET @lcAssets = 'A101'
FROM Assets LEFT OUTER JOIN AssetsHistory ON
AssetsHistory.keyAssets = Assets.keyAssets
WHERE Assets.Number IN (@lcAssets)
However, if I change @lcAssets to 'A101,A102,A103' I don't get an error message or any results. I have tried multiple variations for getting this to work, but seem to be stumped.