i executed the the below function

CREATE FUNCTION SPLIT(@VAL VARCHAR(MAX))

RETURNS @T1 TABLE(COL1 VARCHAR(MAX))

AS

BEGIN

WHILE CHARINDEX(',',@VAL)>0

BEGIN

INSERT INTO @T1 VALUES(SUBSTRING(@VAL,1,(CHARINDEX(',',@VAL))-1))

SET @val=SUBSTRING(@VAL,(CHARINDEX(',',@VAL))+1,LEN(@VAL))

END

INSERT INTO @T1 VALUES(@VAL)

RETURN

END

Below mentioned is my SP

--exec Fms_Subject 'M001,L001,D004,B001'

Alter Proc Fms_Subject

@Subject_No Varchar(25)

As

Begin

Set NoCount ON

select Subj_SCode,Subj_SName from ERP_Subj

WHERE (Subj_SCode IN (select Subj_SCode from split(@Subject_No)))

order by Subj_SCode

End

when i executed this SP its showing all the records,it is supposed to show the records pertained to passed values i.e: 'M001,L001,D004,B001'

can you tell me how to get the data pertained to only specified values.