• huum (7/18/2013)


    No no as you mentioned about un necessary repetative part of SP, thats what i meant

    Ahh gotcha. 😉

    Consider this piece of code.

    INSERT INTO @OfficeAccess (lOffice)

    SELECT map.loffice

    FROM map_PatientToOffice map

    INNER JOIN map_PatientAtOfficeToCustodian mapc ON mapc.lpatient = map.lpatient

    AND mapc.loffice = map.loffice

    INNER JOIN @PhysiciansAccess PA ON PA.lphysician = mapc.lPhysician

    WHERE map.lpatient = @lPatient

    --and PA.lPhysician is not null

    UNION

    SELECT map.loffice

    FROM map_PatientToOffice map

    LEFT JOIN map_PatientAtOfficeToCustodian mapc ON mapc.lpatient = map.lpatient

    AND mapc.loffice = map.loffice

    WHERE map.lpatient = @lPatient

    AND isNull(mapc.lPhysician, 0) <= 0

    Those two queries are VERY similar. I am assuming that since you used UNION you want to exclude duplicates?

    I don't have a table to work with to test but I THINK this should do the same thing.

    INSERT INTO @OfficeAccess (lOffice)

    SELECT map.loffice

    FROM map_PatientToOffice map

    INNER JOIN map_PatientAtOfficeToCustodian mapc ON mapc.lpatient = map.lpatient

    AND mapc.loffice = map.loffice

    left /*INNER JOIN*/ @PhysiciansAccess PA ON PA.lphysician = mapc.lPhysician

    WHERE map.lpatient = @lPatient

    and (PA.lPhysician is not null or isNull(mapc.lPhysician, 0) <= 0)

    group by map.loffice

    Assuming that returns the same data this would be a lot simpler. I think you could do something similar to the first insert/union too.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/