When Multiple values are selected it gives an error incorrect syntax near ','

• rasikawtc

  Old Hand

  Points: 312

  More actions

  May 6, 2014 at 12:58 pm

  #290976

  Hi ,

  I have a report which works fine with single value parameter but when I select multiple values I get an error incorrect syntax near ','

  Here is the query

  If @PtHasHadApptType = 'YES'

  BEGIN

  If '349' IN (@Facility)

  BEGIN

  SELECT *

  FROM #FINAL a

  INNER JOIN CentricityPS.DBO.LOCREG lr

  ON a.apptFacilityId = lr.FacilityId

  WHERE lr.ABBREVNAME LIKE 'STJ%'

  AND (@AllApptTypeSelected = 1 OR ApptTypeId IN (@ApptTypeId))

  AND (@AllPCPSelected = 1 OR DoctorFacilityId IN (@PCP))

  END

  ELSE

  SELECT *

  FROM #FINAL

  WHERE ISNULL(apptfacilityid,'') = @Facility

  AND (ApptResourceFullName IS NOT NULL OR ApptType LIKE '%Transportation%')

  AND (@AllApptTypeSelected = 1 OR ApptTypeId IN (@ApptTypeId))

  AND (@AllPCPSelected = 1 OR DoctorFacilityId IN (@PCP))

  END

  ELSE

  BEGIN

  If '349' IN (@Facility)

  BEGIN

  SELECT *

  FROM #FINAL a

  INNER JOIN CentricityPS.DBO.LOCREG lr

  ON a.ApptFacilityId = lr.FacilityId

  WHERE lr.ABBREVNAME LIKE 'STJ%'

  AND (@AllPCPSelected = 1 OR DoctorFacilityId IN (@PCP))

  AND PID NOT IN (SELECT DISTINCT PID

  FROM #FINAL a

  INNER JOIN CentricityPS.DBO.LOCREG lr

  ON a.ApptFacilityId = lr.FacilityId

  WHERE lr.FacilityId LIKE 'STJ%'

  AND (@AllApptTypeSelected = 1 OR ApptTypeId IN (@ApptTypeId)))

  END

  ELSE

  BEGIN

  SELECT *

  FROM #FINAL

  WHERE ISNULL(apptfacilityid,'') = @Facility

  AND (ApptResourceFullName IS NOT NULL OR ApptType LIKE '%Transportation%')

  AND (@AllPCPSelected = 1 OR DoctorFacilityId IN (@PCP))

  AND PID NOT IN (SELECT DISTINCT PID

  FROM #FINAL

  WHERE (ISNULL(ptfacilityid,'') = @Facility AND ISNULL(apptfacilityid,'') = @Facility)

  AND (@AllApptTypeSelected = 1 OR ApptTypeId IN (@ApptTypeId)))

  END

  END

  Thanks For your help.