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
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.
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.