Thank it is working. But I am having problem when I pass this into my filter condition it does not retrieve records ...I am not sure whether I am passing this correctly..Below is the procedure and its Script.. I have pointed out the variable in bold where I want to pass this string and wanna output..exec [dbo].[sp_MdlModuleWise] ' Furniture Retail Module 1' ,'Barnetts South Africa,Bradlows South Africa'ALTER procedure [dbo].[sp_MdlModuleWise] @Module varchar(100),@Chain varchar(max)asset @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 #passeddrop table #resulltdrop table #NotPassedGO
Vijay is here to learn something from you guys.