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/