CODEWITHSUNDEEP
sql-serverstored-proceduresjunction-table
M S
M S

Reputation: 7

Stored procedure insert into junction table

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

Upvotes: 0

Views: 255

Answers (1)

Ross Bush
Ross Bush

Reputation: 15175

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.

Upvotes: 1

Related Questions