Best practices for scope of returned data per stored procedure

Question

I have the following situation:

table1
------------
ID  Name    Param1  Param2  Param3  RegistrationID
1   Test    Value1  Value2  Value3  101

table2
------------
RegistrationID  LangID  Value
101             EN  English-Value
101             DE  German-Value
101             IT  Italian-Value

I am interested in what are good programming conventions. For example

1. Return two stored procedures

   select id, name, param1, param2, param3, registrationID from table1 where id = @ID
   
   select registrationID, langid, value from table2 where registrationid = @RegistrationID
   

   If necessary on a different layer merge these data into a single class.

2. Have one stored procedure that will return everything

   select id, name, param1, param2, param3, registrationID, langid, value from table1
   inner join tabl2 on table.registrationid = table1.registrationid
   where id = @ID
   

   And create a class from returned data.

The approaches are different, because solution 1 will have to access DB twice but it is easier to reuse stored procedures since they do not join objects (tables). In solution 2 only one trip to DB is needed, but stored procedure gets messy and returns both foreign key and all its values.

I know there is no one answer for all, but what is a good practice? I lean towards solution 1, but I am slightly concerned regarding DB round trips.

Answer 1

If you data requirement is the joined data (solution 2) then tailor your stored procedure for that data. Overly reusing a bunch of stored procedures that are select * from table is best case... cumbersome and worst case... high performance impact (think, pulling back more data than you need over the wire).

Let the relational database management system do what it does best, and go with the stored procedure that gives you back the data you need, and only the data you need.