replace(@Chain, ',', ''',''')

ALTER procedure [dbo].[sp_MdlModuleWise] @Module varchar(100),@Chain varchar(max)
as


set @Chain = replace(@Chain, ',', ''',''')
set @Chain= ''''+@Chain+''''
print @Chain


      create table #resullt ( ModuleName varchar(50),
                              chain varchar(100),
                              [Pass] int,
                              fail int)
                              
      select  distinct Userid,
                      ID,
                      pass,
                      modulename,
                      chain_description  into #temp 
                      from [dbo].[v_ModuleReport] where  pass='yes' and ModuleName is not null
                                                  and ltrim(rtrim(modulename)) in (SELECT ltrim(rtrim([Name])) FROM [moodle].[dbo].[mdl_module]) 
                                                  and chain_description in (select distinct chain_description  from v_hr_moodle where chain_description is not null)
                                                  and userid is not null


	;with cte 
	AS 
	(
		select *, row_number () over (partition by userid,modulename,chain_description order by id ) rn  from #temp
	)

	select userid as userid,
	       modulename,
	       chain_description,
	       MAX(rn) as [Max] into #passed  
	       from cte group by userid,
	                         modulename,
	                         chain_description having  MAX(rn)=6


	insert into #resullt(ModuleName,
	                     chain,
	                     [Pass],
	                     fail) 
	             select 
	                     modulename,
	                     chain_description,
	                     COUNT(1),
	                     0 
	                     from  #passed group by 
	                                   modulename, 
	                                   chain_description


	;with cte 
     AS 
    (
         select *, row_number () over (partition by userid,modulename,chain_description order by id ) rn  from #temp
     )

      select userid as userid,
             modulename,
             chain_description,
             MAX(rn) as [Max] into #NotPassed 
             from cte   group by  userid,
								  modulename,
								  chain_description having  MAX(rn)<6


      insert into #resullt(ModuleName,
						   chain,
						   [Pass],
						   fail)  
				   select modulename,
				          chain_description,
				          0,
				          COUNT(1) 
				          from #NotPassed group by modulename, 
												  chain_description
												 									  
           select ModuleName,
				  chain,
				  sum(pass)  Completed,
				  sum(fail) NotCompleted from #resullt where ModuleName =(@Module) 
				                                       and chain in (@Chain)

 
				                                       group by ModuleName,chain
				 
drop table #temp 
drop table #passed
drop table #resullt
drop table #NotPassed

GO

quote:

---

and chain in (@Chain)

---

and ','+@Chain+',' like '%,'+cast(chain as varchar(32))+',%'