使用存储过程将数组插入sql的表

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 :

在webform页面中，我有一个文本框，用户可以插入如下字符串：

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.

我必须首先检查字符串中的那些单词是否存在于关键字中。如果是，请将它们插入到p-tag表中。我可以使用C＃代码执行此操作，但我想使用存储过程。可能吗？注意：我的英语很弱，对不起。

1 个解决方案

#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:

我建议将关键字作为表值参数传递。请参阅此文章以了解有关它的更多信息 - 表值参数。关于如何使用它还有一个很好的C＃示例。然后通过单个查询完成所需的操作相当容易。首先为table-value参数创建一个类型：

    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!

希望能帮助到你！

#1

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