0
  • Table #1: Messages (PK_ID, Subject, Message)
  • Table #2: Messages2Members (FK_MessageID, AuthorID, RecipientID)

To handle multiple message recipients (i.e. CC's) my stored procedure first inserts into tbl.Messages, but to insert into tbl.Messages2Members, I am unsure if I must create a user-defined table to take the RecipientIDs, then use a cursor or loop - or is there an easier way I am missing?

P.S. SCOPE_IDENTITY() provides value for newly inserted FK_MessageID

P.S.S. I can research the code myself but just want confirmation of best practice. TIA

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
M S
  • 7
  • 3

1 Answers1

1

You can send in a UDT into your SP, for sure, however, some folks will often times opt for a comma-delimited list, especially when filtering data. For the latter, you can split the list into a @RecipientIDs to join in queries.

Option 1

CREATE PROCEDURE MessageInsert(…@RecipientIDTable RecipientIDTable)--Or more generically IntegerIDTable :)

Option 2

CREATE PROCEDURE MessageInsert(…@RecipientIDList NVARCHAR(MAX))
AS
    DECLARE @RecipientIDTable TABLE(RecipientID INT)
    INSERT @RecipientIDTable SELECT ID FROM dbo.MySpliterFunction(@RecipientIDList)

Example of usage 

@MessageID = @@IDENTITY

INSERT INTO Messages2Members (MessageID, FromID, RecipientID)
SELECT @MessageID, @FromID, R.RecipientID FROM @RecipientIDTable R

Option 1 becomes more performant as the size of the list grows, however, I doubt you will ever be worried by that if your payload is CC's to a message.

Ross Bush
  • 14,648
  • 2
  • 32
  • 55
  • Thanks for the reply -- and the performant information is excellent. However, my question is not handing the UDT or CSV but how to insert say three rows (one for each recipient) into the Messages2Members table. I am relatively noob so it maybe is as easy as an INSERT INTO dbo.Messages2Members (AuthorID, ReaderID, MessageID) Select * from ( (Select AuthorID), (Select Recipients from UDT), (Select ScopeIdentity) ) but I am struggling with getting that syntax to work (and am not finding the right terminology to google to research). – M S Dec 08 '18 at 23:35
  • OK turns out that a simple INSERT INTO . . . SELECT FROM is all that was needed (i.e. no cursor or loop). Hopefully I won't be a noob forever ;) – M S Dec 09 '18 at 00:40
  • doh!, I totally botched that, I had the select and insert arguments flipped around. I corrected above, thanks. – Ross Bush Dec 10 '18 at 00:52