sql - 如何操作传递给存储过程的用户定义表类型的表值参数中的数据？

Question

如果这个问题有点含糊，我提前道歉 - 我相当确定这是一个概念问题，而不是语法问题。

我正在使用 MSSQL (SQL Server 2008) 并且我有一个存储过程，它采用用户定义表类型的表值参数(它由一段 . NET 代码，它正在传递一个数据表)。

我遇到的问题如下:
我需要操作该表变量中的数据，但是变量本身是只读的(并且必须是为了传递到存储过程中，据我所知，因为它是用户定义的表类型)。

我已经考虑了从 @TVP 中选择所有内容到 #temp 表中并在最终插入到目标表中之前操作 #temp 的明显解决方案。但是，我希望能够调用多个存储过程来对数据进行操作，这意味着在调用者和被调用者之间来回传递表变量。需要完成的数据操作量很大，这就是为什么我试图将其拆分为多个存储过程。据我所知，这是不可能的，因为 TVP 必须被装饰为 READONLY 并且 #temp 表不能作为参数传递给存储过程。我考虑过使用全局 ##temp 表，但是可以针对不同的数据集同时多次调用此过程(这也是我不将其选入临时物理表的原因)。

从概念上讲，我在这里缺少什么吗？实现这一目标的最佳方法是什么？我愿意使它成为一个庞大的存储过程——但为了使其更易于维护，我宁愿不这样做。

回顾一下:

我有 P_CALLER、P_CALLEE_1 和 P_CALLEE_2(显然不是真名)。

1. P_CALLER 接收 @SomeTable 作为 TVP。

2. P_CALLER 需要将@SomeTable 传递给 P_CALLEE_1 以操作数据，然后将修改后的结果传递回 P_CALLER。

3. P_CALLER 做同样的事情，这次调用 P_CALLEE_2，后者也将它传回。

4. P_CALLER 最终插入 DestinationTable。

我尝试使用谷歌搜索来寻找答案，但似乎没有一个结果与实际情况完全相符。任何建议将不胜感激。

Answer 1

您可以在父过程中创建一个临时表并在子过程中对其进行操作。

这是一个粗略的例子；当然，在生产环境中，您可以通过一些防御性编程/事务处理等来加强这一点。

create type CustomTable as table (Id int primary key, Name varchar(10));
go

create procedure dbo.Worker1
as
begin
    delete from #work where Id > 5;
end
go

create procedure dbo.Worker2
as
begin
    insert into #work
        select -1, 'New One';
end
go

create procedure dbo.Driver (@input CustomTable readonly)
as
begin

    -- #work is created by dbo.Driver but visible to all child procs
    -- its also scoped to this instance of Driver
    select * into #work from @input; 

    -- do complicated work
    exec dbo.Worker1;
    exec dbo.Worker2;

    --return the final set (your insert into DestinationTable)
    select 'FINAL', * from #work;

end
return;

--usage

declare @table CustomTable;
insert into @table  
    values(1, 'One'),(2, 'Two'), (6, 'Six');

exec dbo.Driver @table;

返回:

      Id          Name
----- ----------- ----------
FINAL -1          New One
FINAL 1           One
FINAL 2           Two