2

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.

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.

  • But each part of application need a different set of data. It does not make sense to me that i would have 10 stored procedures retrieving different results (columns) for basically the same object. It is difficult to track and I am not sure i would benefit a performance gain. – John Jul 8 '15 at 15:18
  • Well then you need to do some soul searching. If the objects are substantially the same, but may need a few different columns, I'd agree with you. If you're querying for substantially different data with different columns, then I'd say you need 10 stored procedures. The more concise your result set, the better your database can optimize its lookup. – mgw854 Jul 8 '15 at 15:24
  • But if we stick to the main question, the question was should stored procedure contain just a foreign key of a related table or all the information relevant to that foreign key. For example table2 is containg data about registration. Each call from business logic will not require registration data. Is it better to have variations of stored procedures (getting data from both table1 and table2) or to have one (or set) of stored procedures for table 1 and set for table2 and combine them at the bussiness logic level? – John Jul 8 '15 at 16:46
  • There is no benefit to having "one" stored procedure. You're not creating your class methods to do everything, are you? So why would you model your data logic (i.e. stored procs) like that? You are gaining absolutely nothing. Keep the stored procedures tailored to a specific data requirement. Many stored procedures is completely ok, and very normal. – Thomas Stringer Jul 9 '15 at 13:26
  • @ThomasStringer I dont really agree that you can tailor stored procedure to specifis needs. This is impossible. You will never use 100% of data retreived by sp, or if you want to achieve this you would end up wti 20 sp instead of 2-3. – John Jul 22 '15 at 8:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.