0

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!

that0th3rGuy
  • 1,356
  • 1
  • 15
  • 19

1 Answers1

2

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.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • The batch procedure doesn't do a loop; it performs a `INSERT INTO [User] SELECT FROM @tvp` type of opertaion. I like the idea of placing inserts in one place but the whole system is new, so it's not like either of them is required based on code at the moment. I created them and then realised that I might not even need the one. – that0th3rGuy Sep 09 '13 at 11:00
  • @that0th3rGuy . . . My suggestion, in short, is to have the actual insertions in one place. If multiple interfaces are needed, then fine, create multiple procedures but have them call a single base procedure where the business logic around the inserts can go. – Gordon Linoff Sep 09 '13 at 11:11