ALTER Function [dbo].[fn_Splitter] (@IDs Varchar(100))
Returns @Tbl_IDs Table (ID Int) As
Set @IDs = @IDs + ','
Declare @Pos1 Int
Declare @pos2 Int
Set @Pos1 = CharIndex(',',@IDs,@Pos1)
Insert @Tbl_IDs Select Cast(Substring(@IDs,@Pos2,@Pos1-@Pos2) As Int)
Set @Pos1 = @Pos1+1
ALTER FUNCTION [dbo].[Check_MemberIDCombination]
DECLARE @Msg VARCHAR(20);
declare @count int
select @Ids=(select convert(varchar,Id) + ',' from (select Id from dbo.Fn_Splitter(@Ids)) as tmp order by Id asc for xml path (''))
select @Ids= (select substring(@Ids,1,Len(@Ids)))
set @count = (select count(*) from
select ID, substring(Applicant_Member_Ids,1,len(Applicant_Member_Ids)-1) strings from
select ID ,
(select convert(varchar, Distinct Applicant_Member_Id) + ',' as [text()] from Savings_Account_Applicant where ID =saa.ID order by Applicant_Member_Id for xml path('')) as Applicant_Member_Ids
from Savings_Account_Applicant saa
group by ID
) as Savings_Account ) tab1
SET @Msg = 'Already Exists'
SET @Msg = 'Available'
now i am executing it by as below input
select dbo.Check_MemberIDCombination('41,51') as Msg
the above '150,150,150,150' is already existed in the table
but here i am getting the Msg as 'Available' instead of 'Already Exists'