将测试数据传递给 SQL 中的表值参数 [英] Pass test data to table-valued parameter within SQL

问题描述

是否可以使用 SQL EXEC 将数据传递给存储函数的表值参数，如果可以，如何传递?

Is it possible, and if so how, to pass data to a table-valued parameter of a stored function using SQL EXEC?

我知道如何从 C# 传入数据.我使用表值参数的四个存储过程之一没有产生预期的结果.我想从 SQL 服务器管理工​​作室执行我的 proc 以进行调试，但我无法找到正确的语法，如果这样的语法存在的话.我在文档中没有找到任何相关的内容.

I know how to pass in data from C#. One of my four stored procs using table-valued parameters is not producing the expected results. I'd like to execute my proc from SQL server management studio for debugging purposes, but I am unable to find the correct syntax for doing so, if such a syntax even exists. I haven't found anything relevant in the docs.

我的类型表:

CREATE TYPE [MyNameSpace].[MyTypeTable] AS TABLE( 
//... all my fields
)

我的存储过程:

//... bunch of stuff
ALTER PROCEDURE [MyNameSpace].[MyStoredProc]
@MyTypeTableVar MyTypeTable READONLY 
AS
BEGIN
//Do a bunch of stuff
//Want to test the stuff in here
END

我试过了:

IF OBJECT_ID('tempdb.dbo.#MyTempTable') IS NOT NULL DROP TABLE tempdb.dbo.#MyTempTable;
select top 0 *
into #MyTempTable
//existing table with structure that matches the table-valued param
from MyNameSpace.MyTable;

//...Long insert statement assigning test data to #MyTempTable

EXECUTE MyNameSpace.MyStoredProc @MyTypeTableVar = #MyTempTable;

抛出:

操作数类型冲突:nvarchar 与 MyTypeTable 不兼容

Operand type clash: nvarchar is incompatible with MyTypeTable

推荐答案

您不能使用临时表 - 您必须使用 表变量:

You can't use a temp table - you have to use a table variable:

declare @t [MyNameSpace].[MyTypeTable]
insert into @t (/*columns*/) values
(/* first row */),
(/* second row */)

EXECUTE MyNameSpace.MyStoredProc @MyTypeTableVar = @t;

(如果您有一个包含您关心的数据的现有表，您可以使用 INSERT ... VALUES 填充它，如上所示或 INSERT ... SELECT)

(You can populate it with either INSERT ... VALUES as shown above or INSERT ... SELECT if you have an existing table containing the data you care about)

这篇关于将测试数据传递给 SQL 中的表值参数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！