CODEWITHSUNDEEP
sqlstored-proceduresinner-joinsql-function
Amir Abdollahi
Amir Abdollahi

Reputation: 455

inner join vs stored procedure or Function sql server

I have this schema :

enter image description here

When I want to display the Attend table, instead of DoctorId and PatientId, Employee Name And Patient Name display.

Like this:

First way is to use an inner join :

select 
   a.Id, p.Name, a.Name, a.Start, a.End 
from 
   Patient as p  
inner join 
   (select 
       e.Name, at.Id, at.Start, at.End, at.PatientId
    from 
       Attend as at  
    INNER JOIN 
       Employee as e on at.DoctorId = e.Id) as a on p.Id = a.PatientId

Second way is to use a function or stored procedure - send in the id and get back name

 select 
    a.Id, 
    FindDoctor(a.DoctorId) as Doctor, 
    FindPatient(a.PatientId) as Patient, 
    a.Start, a.EndTime 
 from  
    Attend AS a

Which is the better? Which is the optimized approach?

Upvotes: 1

Views: 2326

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269973

In general, SQL engines do a better job of optimizing queries than of optimizing function calls. I would suggest that you stick with the query, at least at first. This also makes it easier to understand performance issues that might arise.

By the way, there is no need for a subquery for what you want to do. SQL Server is pretty smart, so it probably doesn't affect performance. However, you can write the query as:

select at.Id , p.Name , e.Name , at.Start, at.End 
from Patient p inner join 
     Attend at
     on p.Id = at.PatientId inner join
     Employee as e
     on at.DoctorId = e.Id;

Some people like to embed such queries in stored procedures. If you want to encapsulate this logic, I would suggest a view or table-valued function instead.

Upvotes: 5

Related Questions