Search code examples
sqlstored-procedurestable-valued-parameters

Single SP vs. Multiple

I'm working on a fresh SQL 2008 R2 database. I created a handful of tables and procedures. Currently I have two procedures:

  • [spInsertUser] (@batchID, @firstName, @lastName, ...): creates a single user in the [User] table, associated with a batch.
  • [spInsertUserBatch] (@batchID, @ubUsers): inserts multiple users in the [User] table, all assicated with the same batch.

The second procedure, [spInsertUserBatch], implements a user defined table type containing all the necessary fields to create a user but, obviously, used as a parameter, allows multiple users to be created in a single query.

What I would like to know is this: Should I stick with two procedures, one taking all fields as parameters and the other taking the table as a parameter, or should I get rid of [spInsertUser] and only use [spInsertUserBatch], even when creating a single user?

I like the idea of scrapping the single-user-insertion-procedure for the sake of maintenance (one vs. two) but I'm not sure if there is something I'm unaware of concerning table valued parameters and when using them for inserting single entries.

Single user insertion wil probably happen more often than batch insertion.

Thanks!

Solution

  • If you have two such stored procedures, then it would seem that you have a need for both of them from the calling code. The stored procedure to insert a single record is definitely simpler to call than one that requires a table as an argument.

    I would suggest implementing one of the stored procedures in terms of the other. If the spInsertBatch is looping through and adding records one at a time, instead call the spInsertUser within the loop. If the spInsertBatch is doing the inserts in a single batch, then re-implement spInsertUser by populating a table variable and calling spInsertBatch.

    By doing the actual inserts in one place, you can control logging, data validation, and business logic checks in one place. By keeping two stored procedures, you can simplify the interface for the code making the calls.