基于表值参数的过滤表并将TVP值插入到选择中

Question

我有两个字段的TVP。

sentence_id是过滤器从表中选择记录，这完美的作品。 TVP还包含一个关键字。该TVP看起来是这样的：

Create TYPE [dbo].[sentence_id_list2] AS TABLE(
    [sentence_id] [nvarchar](50) NOT NULL, 
    [keyword] [nvarchar](50) 
) 

我想通过该关键字在结果相同sentence_id所以它看起来是这样的：

Sentence_Identifier,Keyword,Sentence 
123, curious, hello donna, i have a curious problem 

凡sentence_id从TVP传递是123，关键字很好奇。

这是我有的存储过程，只是无法弄清楚如何在结果中包含关键字。

ALTER Procedure [dbo].[chat_Get_Sentences_Table_Value_Parameter] 
@sentence_keys [dbo].[sentence_id_list2] READONLY 
AS 
SELECT TOP (100) PERCENT dbo.chat_All_Records_Sentence_Details.Sentence_Identifier, 
dbo.chat_All_Records_Sentence_Details.Sentence, 

-- how do I write this to insert the keyword from the TVP into the select? 
(SELECT keyword FROM @sentence_keys) AS Keyword 

FROM dbo.chat_All_Records_Sentence_Details 
WHERE (dbo.chat_All_Records_Sentence_Details.Sentence_Identifier 
IN (SELECT sentence_id FROM @sentence_keys)) 

Answer 1

而不是使用IN的simpy使用INNER JOIN的：

SELECT d.Sentence_Identifier, 
     d.Sentence, 
     sk.keyword 
FROM chat_All_Records_Sentence_Details AS d 
     INNER JOIN @sentence_keys AS sk 
      ON sk.sentence_id = d.Sentence_Identifier; 

我已删除，因为这TOP 100 PERCENT将无论如何优化掉，并且还使用别名，以便您的标识符没有那么长。