Insert an array to sql's table with stored procedure

asp.net, c#, sql-server, t-sql

Solution

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!

Problem

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.

Original source