select A.int_ApplicantId,A.vch_UniqueRefNo,A.vch_ApplicantName,case int_Gender when 1 then 'Male' when 2 then 'Female' end as Gender,vch_CorHouseNo, (select vch_DistrictName from M_District where int_DistrictID=A.int_CorDistID and int_StateID=A.int_CorStateID)District,vch_CorPinCode,dtm_DOB, (case int_CategoryId when 1 then 'UR' when 2 then 'SC' when 3 then 'ST' when 4 then 'SEBC' end)Category, (case isnull(bit_PHOH,0) when 1 then 'Yes' else 'No' end ) PWD,(case isnull(bit_SportsPerson,0) when 1 then 'Yes' else 'No' end ) SportsPerson,(case isnull(bit_ESM,0) when 1 then 'Yes' else 'No' end ) ESM, (case isnull(bit_Odia,0) when 1 then 'Yes' else 'No' end ) Odia, (select vch_DDNO from T_Finance_Details F where isnull(F.bit_DeletedFlag,0)=0 and F.vch_uniquerefno=A.vch_uniquerefno)vch_DDNO,A.vch_CorMobileNo , case vch_Remarks when null then (select vch_Remarks from T_CAF_Receive C where C.vch_UniqueRefNo=A.vch_UniqueRefNo and ISNULL(c.bit_DeletedFlag,0)=0) else coalesce ( ( select vch_remarks From M_Remark_Detail where int_Remark_id in ( Select val from UDF_Split ( Select vch_Remarks from T_Caf_Receive C where C.vch_UniqueRefNo=A.vch_UniqueRefNo and ISNULL(C.bit_DeletedFlag,0)=0 ) ),+',' ,'' ) )end As vch_Remarks ,(case isnull(A.bit_ReceiveStatus,0) when 1 then '1' else '0' end ) ReceiveStatus from T_ApplicantDetails_Temp A where isnull(A.bit_DeletedFlag,0)=0 and ISNULL(A.bit_ReceiveStatus,0)=1 and ISNULL(A.bit_Rejected,0)=1 and isnull(A.bit_DuplicateStatus,0)=0 and A.vch_AdvertisementNo=@Pvch_AdvertisementNo and A.vch_Post_Code=@Pvch_Post_Code order by A.vch_UniqueRefNo error are Msg 156, Level 15, State 1, Line 23 Incorrect syntax near the keyword 'Select'. Msg 102, Level 15, State 1, Line 25 Incorrect syntax near ')'.
The error is in your invocation of the function UDF_Split. You are trying to pass a select query in as the expression. Either call the function diretcly on the column in question, or better still, rewrite this. It seems like you are trying to use a comma separated list as a way of builidng an IN statement, by splitting the comma separated list into elements.