How to Passing array parameters to stored procedure in?

Question

I have these tables :

I have an array like cat1,cat2,cat3,cat4 that want passed to sql server stored procedure.
then check that them exist in PaperKeywors's table or not . If yes, insert PKeyID of theme and PID (for example PID=1) to PaperTag and if no return them.
I know i must using TVP , and wrote these code in sql :

CREATE TYPE PaperKeywordsType AS TABLE
        ( PKeyword nvarchar(200) ,PKeyID int  )

And :

Create PROCEDURE [dbo].[InsertPaperTag]
  (@dt AS PaperKeywordsType READONLY ,  @PID int)
AS
BEGIN
  SET NOCOUNT ON;

  INSERT dbo.PaperTags(PID,PKeyID) 
  SELECT @PID,PaperKeywords.PKeyID FROM @dt tvp 
  inner join PaperKeywords on PaperKeywords.PKeyword = tvp.PKeyword
END

But it's not work for me. why?

Answer 1

From C# you need to create a DataTable with the values and pass that to the stored procedure. See this SO Post. You can also pass the values as a delimited string to the stored procedure and use a function in TSQL to convert the values to a table. See this post for a few examples.