sql - 如何将 xml 数据从一个存储过程传递到另一个存储过程？

Question

我有 2 个存储过程用于分配，我想做的是将 xml 输出从一个存储过程传递到另一个存储过程并将其放入一个变量中，我知道 ex1.xml_sp1 在使用 EXEC 调用它时返回一个 int 并且显然，当尝试选择它时，它会返回 null，因为 @x 是 xml 数据类型。

我想做的是从存储过程 1 中检索 xml 数据并将其存储到存储过程 2 中的 @x。

有什么办法吗？

存储过程 1:

ALTER PROC [ex1].[xml_sp1]
@careteamid int
as

select CareTeams.CareTeamID, Doctors.DoctorID, Doctors.DoctorName,
   CareTeamDoctors.DateJoined, CareTeamDoctors.CurrentMember
from dbo.CareTeamTbl as CareTeams
inner join dbo.CareTeamDoctorTbl as CareTeamDoctors on
CareTeams.CareTeamID = CareTeamDoctors.CareTeamID
inner join dbo.DoctorTbl as Doctors on
CareTeamDoctors.DoctorID=CareTeamDoctors.DoctorID
where CareTeamDoctors.CareTeamID = @careteamid
and CareTeamDoctors.DoctorID = Doctors.DoctorID
for xml auto, root('thedata')

存储过程 2:

ALTER PROC [ex1].[xml_sp2]
@careteamid int
as 
declare @x xml

exec @x = ex1.xml_sp1
          @careteamid = @careteamid

select @x as XMLData

Answer 1

I want to do is retrieve and store the xml data from sproc 1 in to @x in sproc 2.

您可以使用 OUTPUT parameters 轻松实现它:

CREATE PROCEDURE [xml_sp1]
   @careteamid INT,
   @xml_output XML OUTPUT
AS
BEGIN
   SET @xml_output = (SELECT * FROM ... FOR XML AUTO, root('thedata'));
END;
GO

CREATE PROCEDURE [xml_sp2]
   @careteamid INT
AS
BEGIN
   DECLARE @x XML;

   EXEC [xml_sp1]
     @careteamid,
     @x OUTPUT;

   SELECT @x AS XMLData;
END;
GO

最后的调用:

EXEC [xml_sp2] @careteamid = 1;

LiveDemo

考虑使用 BEGIN/END block 并以 ; 结束每个语句以避免可能出现的讨厌问题。

可能的共享数据方法的完整列表 How to Share Data between Stored Procedures by Erland Sommarskog