如何将数据集合传递到SQL Server 2003中的T-SQL存储过程

I have a stored procedure that ideally should be able to accept a list/table of NVARCHARs from the database client. I'm aware of table parameters in SQL Server 2008 but I'm stuck with running SQL Server 2003.

我有一个存储过程,理想情况下应该能够从数据库客户端接受NVARCHAR的列表/表。我知道SQL Server 2008中的表参数,但我坚持运行SQL Server 2003。

Currently, I'm concatenating the strings with a separator character on the client side, passing the resulting string as a NVARCHAR parameter, and then teasing apart the string on entry to the stored procedure, but this leaves much to be desired.

目前,我将字符串与客户端上的分隔符连接起来,将生成的字符串作为NVARCHAR参数传递,然后在输入时将字符串拆分为存储过程,但这还有很多不足之处。

2 个解决方案

#1

Have you looked at passing in XML?

你看过传递XML了吗?

So, for XML a little like:

所以,对于XML有点像:

<ArrayOfService xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <Service Id="2" Name="AUSTRALIA" Code="AUS" />
  <Service Id="10" Name="FAR EAST" Code="FEE" />
</ArrayOfService>

In SQL Server 2005 you could do:

在SQL Server 2005中,您可以:

-- Lookup Services
DECLARE @ServiceXml AS XML

CREATE TABLE #Service
(
    Id INT,
    [Name] VARCHAR( 50 ),
    Code VARCHAR( 10 )
)

INSERT INTO #Service
(
    Id,
    [Name],
    Code
)
SELECT
    CASE
        WHEN LegsTbl.rows.value('@Id', 'nvarchar(255)') = '' THEN NULL
        WHEN LegsTbl.rows.value('@Id', 'int') = 0 THEN NULL
        ELSE LegsTbl.rows.value('@Id', 'int')
    END AS Id,
    LegsTbl.rows.value('@Name', 'varchar(50)') AS [Name],
    LegsTbl.rows.value('@Code', 'varchar(50)') AS TopazCode
FROM
    @ServiceXml.nodes('/ArrayOfService/Service') LegsTbl(rows)

Or SQL Server 2000:

或者SQL Server 2000:

DECLARE @ServiceXml AS NTEXT
DECLARE @iServiceXml AS INT

--Create an internal representation of the XML document.
EXEC sp_xml_preparedocument @iServiceXml OUTPUT, @ServiceXml

CREATE TABLE #Service
(
    Id INT,
    [Name] VARCHAR( 50 ),
    Code VARCHAR( 10 )
)

INSERT INTO #Service
(
    Id,
    [Name],
    Code
)
SELECT
    Id,
    Name,
    Code
FROM
OPENXML( @iServiceXml, '/ArrayOfService/Service', 3)
    WITH (Link8Id   int '@Id',
          Name  varchar(50) '@Name',
          Code  varchar(10) '@TopazCode')

#2

IN SQL2005 (which I assume you mean?) I build up an XML string and pass it

在SQL2005中(我假设你的意思是?)我构建了一个XML字符串并传递它

CREATE PROCEDURE dbo.ig_SelectRecentConfigurableAppsByMakes
(
    @MakesXML       XML,    -- <makes><value>GMC</value>...</makes>
    @TopN           INT
)
AS
    DECLARE @Makes TABLE (Make NVARCHAR(30))
    INSERT INTO @Makes
    SELECT ParamValues.make.value('.','NVARCHAR(30)')
      FROM @MakesXML.nodes('makes/value') AS ParamValues(make)
    ;

#1