Insert an array to sql's table with stored procedure

Question

I have a table like this :

keyword (table name) 
---------------- 
kid key 
---------------- 
1 asp.net 
2 php 
3 C#‎ 
4 vb

In a webform page, i have a textbox that user can insert string like below :

asp.net,php,C#‎,vb,css

i must insert this string in another table like below:

p-tag (table name)
----------------
pid kid
----------------
1 1
1 2
1 3
1 4

i must first check that those word in string exist in Keyword,or not. if yes insert them in p-tag table. I can perform this with C# code , but i want do with stored procedure.
Is it possible?
note : my english is weak, sorry.

Answer 1

I suggest passing the keywords as a table-value parameter. Refer to this article to find out more about it - Table-Valued Parameters. There's also a good C# example on how to use it. Then it is fairly easy to accomplish what you need with a single query. First create a type for table-value parameter:

 CREATE TYPE dbo.TableTypeName AS TABLE
 ( keyName nvarchar(50) )

Then use it in the procedure like that:

CREATE PROCEDURE sp_UpdateTags 
 (@tableParameter dbo.TableTypeName READONLY, @tagId INT)
AS
INSERT [p-tag](pid, kid)
SELECT @tagId, K.kid from @tableParameter tP
inner join [keyword] K on key = tP.keyName

This will insert only values which exist in keyword table.

Hope it helps!