ALTER Function [dbo].[fn_Splitter] (@IDs Varchar(100))
Returns @Tbl_IDs Table (ID Int) As
Begin
Set @IDs = @IDs + ','
Declare @Pos1 Int
Declare @pos2 Int
Set @Pos1=1
Set @Pos2=1
While @Pos1<len(@ids)>
Begin
Set @Pos1 = CharIndex(',',@IDs,@Pos1)
Insert @Tbl_IDs Select Cast(Substring(@IDs,@Pos2,@Pos1-@Pos2) As Int)
Set @Pos2=@Pos1+1
Set @Pos1 = @Pos1+1
End
Return
End
ALTER FUNCTION [dbo].[Check_MemberIDCombination]
(
@Ids VARCHAR(1000)
)
RETURNS VARCHAR(20)
AS
BEGIN
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
where tab1.strings=@Ids)
if @count>0
begin
SET @Msg = 'Already Exists'
end
ELSE
BEGIN
SET @Msg = 'Available'
END
RETURN @Msg
END
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'