What is the best approach to writing a stored procedure for a 1:N relationship performing an insert?

Question

If I have two tables A and B:

A(AId, a1, a2) B(BId, AId, b1, b2, b3)

On first thought , I wanted to write a sp (stored procedure) incorporates the two insert statements.

But, on a second I thought I felt it would be nicer if I had a separated sp to do an insert for the table B and another to do an insert for table A while calling the insert sp for B from within A.

Please which is the best approach?

Answer 1

That depends: If you assume that you will be able to reuse the SP for B in another context (i.e. outside the SP for A), make a separate SP. Otherwise, having just one SP (in particular, if it's only a simple SP with two INSERT statements) might reduce the complexity of your DB layer.

Answer 2

I would say that from maintenance point of view, it is better to have two procedures, since it makes the design more clear and two short procedures are easier to understand and design than a large one. I don't know anyway if this approach would have any performance implications.

Answer 3

Do two separate procedures. Even if you don't see a need to separate them now, usually the point will come in future, where you would like to do a separate insert on only one of both. Then you'll be happy to have split up the SPs. (separation of concerns)

Answer 4

You could also create a view on two tables and use INSTEAD OF INSERT trigger on the view. This way you treat everything as one table .

CREATE VIEW dbo.vAB
AS
SELECT x.AId, a1, a2, BId, b1, b2 ,b3
FROM dbo.A AS x JOIN dbo.B AS y on x.AID = y.AID
go

CREATE TRIGGER dbo.myInsrt
ON dbo.vAB
INSTEAD OF INSERT
AS
BEGIN
 -- your code here to deal with both tables
END