SQL 存储过程 - 在内部使用参数
我有一个存储过程，我正在向其中传递两个参数。一个参数是表值参数，另一个是 nvarchar。这是存储过程：
ALTER PROCEDURE [dbo].[_sp_TestProc]
@P1 As [dbo].[FileIdTableType] Readonly,
@P2 As NVARCHAR (MAX)
AS
BEGIN
SET NOCOUNT ON;
SELECT DISTINCT [Field1], [Field2], [Field3]
FROM [MyTable] WHERE [Field1] IN (@P1)
AND [Field2] IN (@P2)
END
然后，从代码中，我将 DataTable 作为 @P1 传递，并将一串值作为 @P2 传递，看起来像 "'Value1', 'Value2', 'Value3', 'Value4'"
是 @P2 给我带来了问题。表类型工作正常。通常，当在存储过程中使用此类参数时，我会在内部创建一个字符串，使用参数格式化字符串，然后调用 sp_executeSql 来运行查询。但是，当使用表值参数时，这将不起作用。
有谁知道如何像这样使用表值参数和 nvarchar 吗？我确信在尝试正确格式化时我错过了一些简单的东西。 谢谢，
-斯科特
I have a stored proc that I am passing two parameters into. One parameter is a table-valued parameter and the other is a nvarchar. Here is the stored proc:
ALTER PROCEDURE [dbo].[_sp_TestProc]
@P1 As [dbo].[FileIdTableType] Readonly,
@P2 As NVARCHAR (MAX)
AS
BEGIN
SET NOCOUNT ON;
SELECT DISTINCT [Field1], [Field2], [Field3]
FROM [MyTable] WHERE [Field1] IN (@P1)
AND [Field2] IN (@P2)
END
From code, I am then passing in my DataTable as @P1 and a string of values as @P2 that looks like "'Value1', 'Value2', 'Value3', 'Value4'"
It's @P2 that is causing me problems. The Table Type works fine. Normally when using parameters such as this in my stored procs, I create a string internally, format my string with the parameters, then call sp_executeSql to have the query run. However, this won't work when using Table-Valued parameters.
Does anyone know how to work with table valued parameters along with nvarchar like this? I'm sure I'm missing something simple when trying to format this correctly.
Thanks,
-Scott
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
您需要首先将@P2解析为临时表或表变量。您不能将 IN 运算符与列出多个值的单个字符串一起使用。
请参阅这个问题。
You need to parse @P2 into a temp table or table var first. You can't use the IN operator with a single string that lists a number of values.
See this SO question.