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