stored procedures - Many to many relationship in SQL server - To add reference in foreign table when insertion does not happen in base table -

i have 2 tables user , item. user table have user details , item table have unique entries of item.

now creating many many relationship there separate table called list references primary keys of both table foreign key. each user pass csv string contain items wants. these items added item table , id along user id placed in list table.

the tables created this....

create table dbo.item ( item_id int identity(1,1) not null, item_name varchar(30) not null, primary key (item_id) );  create table dbo.list ( item_id int not null, users_id varchar(11) not null, ); 

the stored procedure looks this...

alter procedure [dbo].[entry]      @user_id varchar(11)     @items varchar(max)=null  begin     insert dbo.item     output inserted.item_id @outputtbl(item_id)         select item_name        [dbo].[split] ( @items, ',')  -- call split function       insert dbo.list        select item_id, @user_id         @outputtbl end go 

now when there duplicate of item name, want skip insertion in item table entry alone should placed in list table.

for example if user1 has given list "soap,shampoo" , inserted ids 1 , 2 , refrences added in list table. when user2 makes list "brush,soap" name soap should not duplicated in item table id of soap should added in reference table like

    userid    |   itemid   ---------------------------     user1         1      user1         2      user2         3      user2         1 

this seems hard me because retrieve id of inserted records , place them in list table. how place them in list table when name present.

i believe may trying @ once. proc appears have following 3 concerns:

1. splitting out list of items names @items input comma delimited string
2. maintaining dbo.item table unique set of 'seen' item names (i.e. adding new items table)
3. maintaining dbo.list user : itemid mappings irrespective of whether item 'new' or 'existing'.

i break concerns accordingly:

create procedure [dbo].[entry]      @user_id varchar(11),     @items varchar(max)=null begin     declare @itemnames table (name nvarchar(50));     declare @itemids table (item_id int);      insert @itemnames(name)         select item_name [dbo].[split] ( @items, ',');      insert dbo.item(item_id)         select name         @itemnames new         not exists(select 1 dbo.item i.item_id = new.name);      insert dbo.list(itm.item_id, user_id)        select item_id, @user_id         @itemnames new         inner join dbo.item itm         on itm.item_id = new.name; end; 

sqlfiddle here