Stack Exchange
COALESCE Doubles results values
in function
SELECT @ProcedureName = COALESCE(@ProcedureName + ',', '') + ProcedureName
FROM V_Procedures P 
inner join MedicalRecords MR on P.Medical_Record=MR.Medical_Record 
where ProcedureName IS NOT NULL and MR.Admission_No  = @Admission_No 
and Field = 18

getting many doubles seperated by comma, is it possible to avoid it?
For example results values appears more then once as 
CONCHOTOMY,FESS-FUNCTIONAL ENDOSCOPIC (NASAL) SINUS SURGERY,CONCHOTOMY,FESS-FUNCTIONAL ENDOSCOPIC (NASAL) SINUS SURGERY,SUBMUCOUS RESECTION OF NASAL SEPTUM


One way is to, add GROUP BY as GROUP BY ProcedureName in the query.

You seems want distinct :
SELECT DISTINCT @ProcedureName = COALESCE(@ProcedureName + ',', '') + ProcedureName
FROM V_Procedures P INNER JOIN 
     MedicalRecords MR 
     ON P.Medical_Record=MR.Medical_Record 
WHERE ProcedureName IS NOT NULL AND 
      MR.Admission_No  = @Admission_No AND 
      Field = 18;


Instead COALESCE, try with CONCAT. And also GROUP BY.
DECLARE @ProcedureName VARCHAR(max);

SELECT @ProcedureName = CONCAT(@ProcedureName + ',', ProcedureName)
FROM V_Procedures P
JOIN MedicalRecords MR ON P.Medical_Record = MR.Medical_Record 
WHERE ProcedureName IS NOT NULL AND MR.Admission_No  = @Admission_No 
  AND Field = 18
GROUP BY ProcedureName

The trick is that CONCAT won't return NULL when a NULL is added to it. But + does. So because  @ProcedureName is NULL at first, the result won't start with a comma.

Are you sure it will work for all the database ? because OP has not tagged the database he is using.

@Susang  He tagged t-sql. So it's either MS Sql Server or Sybase ASE. Probably the former.
