sql - Creating a stored procedure to insert specific data into 1-2 tables -

i have 2 tables,

idea(idea_id int primary key, idea_name varchar(30)) 

and

keyword(idea_id int, keyword varchar(15), weight numeric(1,0)) 

i looking create stored procedure inserts data idea and/or keyword table. format of input ideally (idea_name, k1,w1,k2,w2,k3,w3...etc) ks being keywords , ws being weight.

i if idea name can't found in idea table, new idea name first inserted idea table, , other data inserted keyword table. possible have stored procedure check input pattern make sure has correct formatting , correct data types, showing string if there issue?

thanks in advance help, quite new stored procedures , mssql freetext search engine! there other relevant tags include?

cheers

please don't use comma separated list, there better ways of doing this, in opinion there no place delimited strings in sql.

if using sql-server 2008 or later can use table valued parameters pass keywords store procedure:

create type dbo.keywords table (keyword varchar(15) not null, weight numeric(1, 0)); 

your procedure like

create procedure dbo.saveidea @ideaname varchar(30), @keywords dbo.keywords readonly     declare @ideaid int = ( select  top 1 idea_id                                 dbo.idea                                idea_name = @ideaname                         );      if @ideaid null         begin             insert dbo.idea (idea_name) values (@ideaname);             set @ideaid = scope_identity();         end     merge dbo.keywords t     using @keywords s         on t.idea_id = @ideaid         , t.keyword = s.keyword     when not matched insert (idea_id, keyword, weight)         values (@ideaid, s.keyword, s.weight)     when matched , t.weight != s.weight update         set weight = s.weight     output $action, inserted.*;  go 

the first check see if idea exists name, if no new row inserted, otherwise new idea added. merges keywords. if exist idea not added, if weight different updated. if keyword doesn't exist inserted.

then use can use like:

declare @keywords dbo.keywords;  insert @keywords (keyword, weight) values     ('test 1', 1),     ('test 2', 2),     ('test 3', 3),     ('test 4', 5),     ('test 5', 4);  execute dbo.saveidea 'test idea', @keywords;  select  *    dbo.idea;  select  *    dbo.keywords; 

sql fiddle

although more practical call application layer.