declare @targetSystem nvarchar(30) = 'All'

select  
	Dept_Name = replace(dbo.fn__SplitValue(o.Ident_Org,'|',1),',',''),
	
	o.CustomProperty02 as 'Dept_Code',
	Division_Name = dbo.fn__SplitValue(o.Ident_Org,'|',2),
	
	o.CustomProperty03 as 'Division_Code',
	Position_Name = dbo.fn__SplitValue(o.Ident_Org,'|',3), 
	
	o.CustomProperty04 as 'Position_Code',
	Job_Name = replace(dbo.fn__SplitValue(o.Ident_Org,'|',4),',',''),
	
	o.CustomProperty05 as 'Job_Code',
	'' as Service_Name,
	'' as Service_Code,
	System_Name =
		Case  ug.XProxyContext when 'ADS' then 'AD'else ug.XProxyContext end,
	System_Type =
		Case   when ug.XProxyContext = 'ADS' or ug.XProxyContext='ADS' then 'Microsoft ADS'else 'APP' end,
	Application_Name=
		Case  ug.XProxyContext when 'ADS' then 'AD'else ug.XProxyContext end,
	'' as ROLE_ID,
	ug.cn as 'Role_Name',
	Role_Values=
		Case when ug.XProxyContext ='AD' or ug.XProxyContext ='ADS' then ug.cn else ug.TSTIDM_GroupKey end

from org o
	join OrgHasUNSGroup ou on ou.UID_Org = o.UID_Org
	join UNSGroup ug on ug.UID_UNSGroup = ou.UID_UNSGroup
	---join Department d on d.ObjectID = o.CustomProperty02
	---join Org jdo on jdo.ShortName = o.CustomProperty05
	---join Org pjdo on pjdo.ShortName = o.CustomProperty04
	---join ProfitCenter pc on pc.AccountNumber = o.CustomProperty03 
where o.UID_OrgRoot in (select UID_OrgRoot from OrgRoot where Ident_OrgRoot = N'Role Matrix')
		and ug.XProxyContext in     
	case  
		when @targetSystem = 'All' Then ('FINSOFT','CARDIX','CARDIX_ATM','ADS','AD')
	Else 
		(@targetSystem)
	End
order by 5,8

	AND 
	(
		(@targetSystem = 'All' AND ug.XProxyContext IN ('FINSOFT','CARDIX','CARDIX_ATM','ADS','AD'))
		OR
		(ug.XProxyContext = @targetSystem)
	)

quote:

---

Originally posted by sunitabeck

You can change that part of the where clause to this:

	AND 
	(
		(@targetSystem = 'All' AND ug.XProxyContext IN ('FINSOFT','CARDIX','CARDIX_ATM','ADS','AD'))
		OR
		(ug.XProxyContext = @targetSystem)
	)

---