SQL Server：存储过程输入表变量解决方法

Question

我正在尝试找到一个好的解决方法来解决无法使用表变量作为存储过程的输入的问题。我想将一条记录插入到基表中，将多条记录插入到数据透视表中。我最初的思考过程使我想要一个存储过程，该存储过程具有基表的单独输入和数据透视表记录的单个列表输入，即：

create proc insertNewTask (@taskDesc varchar(100), @sTime datetime, @eTime datetime, @items table(itemID int))
as
 begin
  declare @newTask table(newID int)
  insert into tasks(description, sTimeUTC, eTimeUTC)
  output inserted.ID into @newTask
  values(@taskDesc, @sTime, @eTime)

  insert into taskItems(taskID, itemID)
  select newID, itemID
  from @newTask cross join @items
 end

如前所述，由于表变量输入，上述代码将不起作用， @items （我认为主要是由于变量范围问题）。那么，有什么好的解决方法吗？

原始问题
我有三个表：

CREATE TABLE items
(
  ID          int PRIMARY KEY,
  name        varchar(20),
  description varchar(100)
)

CREATE TABLE tasks
(
  ID          int identity(1,1) PRIMARY KEY,
  description varchar(100),
  sTimeUTC    datetime,
  eTimeUTC    datetime
)

CREATE TABLE taskItems
(
  taskID      int,
  itemID      int,
  CONSTRAINT fk_taskItems_taskID FOREIGN KEY (taskID) on tasks(ID),
  CONSTRAINT fk_taskItems_itemID FOREIGN KEY (itemID) on items(ID)
)

带有一些初始项目数据：

insert into items (ID, name, description)
select 1, 'nails', 'Short piece of metal, with one flat side and one pointed side' union
select 2, 'hammer', 'Can be used to hit things, like nails' union
select 3, 'screws', 'I''m already tired of writing descriptions for simple tools' union
select 4, 'screwdriver', 'If you can''t tell already, this is all fake data' union
select 5, 'AHHHHHH', 'just for good measure'

我有一些用于创建新任务的代码：

declare @taskDes varchar(100), @sTime datetime, @eTime datetime
select @taskDes = 'Assemble a bird house',
    @sTime = '2011-01-05 12:00', @eTime = '2011-01-05 14:00'

declare @usedItems table(itemID int)
insert into @usedItems(itemID)
select 1 union
select 2


declare @newTask table(taskID int)
insert into tasks(description, sTimeUTC, eTimeUTC)
output inserted.ID into @newTask
values(@taskDes, @sTime, @eTime)

insert into taskItems(taskID, itemID)
select taskID, itemID
from @newTask
    cross join @usedItems

现在，我想要一种简化/简化新任务创建的方法。我的第一个想法是使用存储过程，但表变量不能用作输入，所以它不起作用。我想我可以使用带有插入触发器的视图来做到这一点，但我不确定......这是我最好的（或唯一的）选择吗？

Answer 1

我非常幸运地使用 XML 将数据传递给过程。您可以使用OPENXML (Transact-SQL) 解析 XML。

-- You already had an example of @usedItems 
-- declared and populated in the question
declare @usedItems table(itemID int)
insert into @usedItems(itemID)
select 1 union
select 2

-- Build some XML, either directly or from a query
-- Here I demonstrate using a query
declare @itemsXML nvarchar(max);
select @itemsXML = 
    '<Items>' 
    + (select itemID from @usedItems as Item for xml auto) 
    + '</Items>'

print @itemsXML

-- Pass @itemsXML to the stored procedure as nvarchar(max)

-- Inside the procedure, use OPENXML to turn the XML 
-- back into a rows you can work with easily

DECLARE @idoc int
EXEC sp_xml_preparedocument @idoc OUTPUT, @itemsXML

SELECT  *
FROM    OPENXML (@idoc, '/Items/Item',1)
        WITH (itemID  int)

EXEC sp_xml_removedocument @idoc

结果

<Items><Item itemID="1"/><Item itemID="2"/></Items>
itemID
-----------
1
2