在其他查询中使用参数

Question

我正在创建一个代理作业，该作业正在我的表中查找xml字符串。如果存在尚未运行的xml字符串。我想把它保存为变量、参数或其他什么，这样我就可以用它合并到我的另一个表中。如何将xml字符串保存为任何类型的变量，以便在其他查询中获取/使用它。

DECLARE @XmlDocument AS xml;
SET @XmlDocument = (SELECT TOP 1 XMLData FROM dbo.Integration WHERE Entity = 'Currency' and [Status] = '0');

我一直在研究存储过程，但我不能让它工作，如果这是一个很好的方法，我会非常感谢一个好的解释。

编辑2：这是我要将变量导入的另一个查询。

Create Table #TempTable(IntComp NVARCHAR(100), Active NVARCHAR(100), Currency NVARCHAR(100), [Text] NVARCHAR(100))
BEGIN TRY
DECLARE @XmlDocument AS xml;
SET @XmlDocument = '';
WITH XMLNAMESPACES('http://Bla.Bla.Currency' AS ns0)
INSERT INTO #TempTable (IntComp, Active, Currency, [Text])      
            SELECT 
                    @xmldocument.value('/ns0:Currency[1]/Currency[1]/IntComp[1]', 'NVARCHAR(100)') IntComp,
                    @xmldocument.value('/ns0:Currency[1]/Currency[1]/Active[1]', 'NVARCHAR(100)') Active,
                    @xmldocument.value('/ns0:Currency[1]/Currency[1]/Currency[1]', 'NVARCHAR(100)') Currency,
                    @xmldocument.value('/ns0:Currency[1]/Currency[1]/Text[1]', 'NVARCHAR(100)') [Text]                  
MERGE #TempTable AS T
USING dbo.etCurrency AS S
ON (T.IntComp = S.IntComp and T.Currency = S.Currency)
WHEN MATCHED THEN UPDATE
SET T.IntComp = S.IntComp, T.Currency = S.Currency
WHEN NOT MATCHED THEN INSERT
VALUES(S.IntComp, S.Active, S.Currency, S.Text);
UPDATE dbo.Integration
    SET [Status] = '1' WHERE [Status] = '0';
END TRY
BEGIN CATCH
UPDATE dbo.Integration
    SET [Status] = '99', [StatusMessage] = @@ERROR WHERE [Status] = '0';
END CATCH
DROP TABLE #TempTable;

Answer 1

您只是想创建一个包含该SQL的存储过程吗？如果是的话，怎么样；

create procedure dbo.MyProcedure (@XmlDocument XML)
as
begin
    Create Table #TempTable(IntComp NVARCHAR(100), Active NVARCHAR(100), Currency NVARCHAR(100), [Text] NVARCHAR(100))
    BEGIN TRY
        WITH XMLNAMESPACES('http://Opto.Schemas.Currency' AS ns0)
        INSERT INTO #TempTable (IntComp, Active, Currency, [Text])      
                    SELECT 
                            @xmldocument.value('/ns0:Currency[1]/Currency[1]/IntComp[1]', 'NVARCHAR(100)') IntComp,
                            @xmldocument.value('/ns0:Currency[1]/Currency[1]/Active[1]', 'NVARCHAR(100)') Active,
                            @xmldocument.value('/ns0:Currency[1]/Currency[1]/Currency[1]', 'NVARCHAR(100)') Currency,
                            @xmldocument.value('/ns0:Currency[1]/Currency[1]/Text[1]', 'NVARCHAR(100)') [Text]                  
        MERGE #TempTable AS T
        USING dbo.etCurrency AS S
        ON (T.IntComp = S.IntComp and T.Currency = S.Currency)
        WHEN MATCHED THEN UPDATE
        SET T.IntComp = S.IntComp, T.Currency = S.Currency
        WHEN NOT MATCHED THEN INSERT
        VALUES(S.IntComp, S.Active, S.Currency, S.Text);
        UPDATE dbo.Integration
            SET [Status] = '1' WHERE [Status] = '0';
    END TRY
    BEGIN CATCH
        UPDATE dbo.Integration
            SET [Status] = '99', [StatusMessage] = @@ERROR WHERE [Status] = '0';
    END CATCH
    DROP TABLE #TempTable;
end

然后用；

DECLARE @XmlDocument AS xml;
SET @XmlDocument = (SELECT TOP 1 XMLData FROM dbo.Integration WHERE Entity = 'Currency' and [Status] = '0');
EXEC dbo.MyProcedure @XmlDocument

Answer 2

我使用存储过程解决了它：

create procedure getXML
@xml XML 
as
select XMLData
from dbo.Integration
where Entity = 'Currency' and [Status] = '0';
Go

然后通过以下方式调用此存储过程：

declare @xml XML
exec getXML @xml