Thank You for that.
But how to return the value in column v (table variable column) to be assigned to @HCodes(SP output variable).
The code presently I having is below:
ALTER PROCEDURE [dbo].[eusp_e5_eSM_AS01_MaterialStockRecordReportHdr]
@MaterialItemContainerCode nvarchar(1000),
@HCodes varchar(max) OUTPUT
AS
DECLARE @MaterialCode varchar(max)
SELECT @MaterialCode = materialItemCode FROM local_MaterialsItemsContainers MIC
INNER JOIN local_MaterialsItems MI
ON (MIC.materialItemIncId = MI.materialItemIncId AND MIC.materialItemSqlId = MI.materialItemSqlId AND MI.isDeleted=0x0)
WHERE charindex(MIC.materialItemContainerCode,'MC-00000030-13-0001')<>0
declare @t table (id int identity(1, 1), v varchar(50))
INSERT @t
SELECT LH.hazardCode from Local_MaterialsItems MI
INNER JOIN Local_MaterialsItemsHazards MIH
on MI.materialItemIncId = MIH.materialItemIncId AND MI.materialItemSqlId = MIH.materialItemSqlId
INNER JOIN Local_Hazards LH
on MIH.hazardIncId = LH.hazardIncId AND MIH.hazardSqlId = LH.hazardSqlId
where charindex(MI.materialItemCode,@MaterialCode)<>0
;WITH
E1(i) AS (select 1 from (values (1),(1),(1),(1),(1),(1),(1),(1),(1),(1))dt(i)),
E2(i) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows
cteTally(i) AS
(
SELECT top 50 ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E2
)
select distinct left(substring(','+v, i + 1, 50), charindex(',', substring(','+v+',', i + 1, 50))-1) as v
from @t t, cteTally
where substring(','+v, i, 1) = ','
order by v