2 calls or a single call to sql server

Question

I have an action in my .net application that creates a record in 2 differnet sql server tables. What is the best practise here?

Should the one stored procedure create both records? OR Should a different stored procedure create each record in applicable database and wrap both calls in a transaction so if one fails, both fail?

Which is the more performant?

Answer 1

A stored proc will be more performant, definitely. It will also lead to less code you have to write server-side. The drawback is, you now need to either know how to write the SQL yourself, or have a DBA whom you work with who can write it for you.

In regards to your other question, it would be 1 stored proc that inserts in both tables.

Another thing to consider: IF you have to do a ton of things like this throughout your code, it might be worth learning a good ORM and doing this all in code (Option 2). But if this is sort of a one-off thing you only do in a handful of places, stored proc will be a smarter approach.