如何将浮动数组传递给SQL Server存储过程?

I need to write an array of 36 floats to a SQL Server 2005 database. Is there a best-practices answer which explains how to do this?

我需要将一个36个浮点数组写入一个SQL Server 2005数据库。是否有一个最佳实践的答案来解释如何做到这一点?

I can't write it as a string because we may need to query individual values over millions of rows, and I don't want to have to de-construct each row to read it's value.

我不能把它写为字符串，因为我们可能需要查询数百万行的单个值，而我不希望不得不去构造每一行来读取它的值。

I would also like to avoid passing 36 params to the stored procedure.

我还希望避免向存储过程传递36个参数。

So, what does the conventional wisdom of * recommend?

那么，*的传统智慧推荐了什么?

5 个解决方案

#1

In Sql Server 2008, there are table valued parameters for just this reason. However, in 2005, you're only choices are (1) string joining/splitting or (2) xml.

在Sql Server 2008中，正是因为这个原因，才有表值参数。但是，在2005年，您只能选择(1)字符串连接/拆分或(2)xml。

I'd go with passing XML, and then inserting into a table variable. Here's an example:

我将传递XML，然后插入到表变量中。这里有一个例子:

declare @floatsXml nvarchar(max);
set @floatsXml = '<nums><num val="2.123" /><num val="2.123" /></nums>';

declare @floats table (value float);

insert into @floats
select tbl.c.value('@val', 'float')
from @floatsXml.nodes('/nums/num') as tbl(c);

select * 
from @floats;

I don't have a current sql server install, so my syntax may be slightly off, but it should be mostly right.

我没有当前的sql server安装，所以我的语法可能有点偏离，但应该基本正确。

#2

With SQL Server 2008, you could use a table valued parameter. Since this is SQL 2005, I use a comma separated list and then a split function that casts to the appropriate data type and returns a table.

使用SQL Server 2008，您可以使用表值参数。由于这是SQL 2005，所以我使用逗号分隔的列表，然后使用一个分割函数，将其转换为适当的数据类型并返回一个表。

This is the split function I use:

这是我使用的分割函数:

IF OBJECT_ID('dbo.Nums') IS NOT NULL 
  DROP TABLE dbo.Nums ;
GO

CREATE TABLE [dbo].[Nums]
  (
   [n] int NOT NULL,
   PRIMARY KEY CLUSTERED ([n] ASC)
    WITH (PAD_INDEX = OFF, FILLFACTOR = 100, IGNORE_DUP_KEY = OFF,
          STATISTICS_NORECOMPUTE = OFF, ALLOW_ROW_LOCKS = ON,
          ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
  )
ON
  [PRIMARY] ;
GO

DECLARE @max AS INT,
  @rc AS INT ;
SET @max = 1000000 ;
SET @rc = 1 ;

INSERT  INTO dbo.Nums (n)
VALUES  (1) ;
WHILE @rc * 2 <= @max
  BEGIN
    INSERT  INTO dbo.Nums (n)
            SELECT  n + @rc
            FROM    dbo.Nums ;
    SET @rc = @rc * 2 ;
  END

INSERT  INTO dbo.Nums (n)
        SELECT  n + @rc
        FROM    dbo.Nums
        WHERE   n + @rc <= @max ;
GO

CREATE FUNCTION [dbo].[fn_split]
(@arr nvarchar(4000), @sep nchar(1))
RETURNS table
AS
RETURN
  SELECT    (n - 1) - LEN(REPLACE(LEFT(@arr, n-1), @sep, N'')) + 1 AS pos,
                SUBSTRING(@arr, n, CHARINDEX(@sep, @arr + @sep, n) - n) AS element
  FROM      dbo.Nums
  WHERE     n <= LEN(@arr) + 1
                AND SUBSTRING(@sep + @arr, n, 1) = @sep;
GO

#3

If it was 360 or 3600, I'd say XML or a string, but 36 is probably still small enough to consider parameters.

如果是360或3600，我会说XML或字符串，但36可能仍然足够小，可以考虑参数。

The main reason I say that is because when you go to text instead of strongly-typed parameters, you have to worry about formatting consistently. And number formatting can be finicky with commas being used for thousands separators in some countries and decimal separators in others. If your client access code runs with different regional settings than expected by the stored procedure, you could find it breaking your app.

我这么说的主要原因是，当您使用文本而不是强类型参数时，您必须始终关注格式化。数字格式可以用逗号来表示，在一些国家中使用了数千个分隔符，而在另一些国家则使用小数分隔符。如果您的客户端访问代码运行的区域设置与存储过程所期望的不同，您可能会发现它破坏了您的应用程序。

#4

I'd recommend passing the floats as an XML parameter, but storing it in the database in 36 separate columns. (I'm assuming here that the floats are distinct in meaning from each other and that you'll want to pull them out individually.)

我建议将浮点作为XML参数传递，但将它存储在数据库中的36列中。(我假设这些花车在意义上是不同的，你会想把它们单独拉出来。)

As you said, you don't want to have to de-construct each row to read its value, so you'll need 36 columns. (This is also good practice). But you can pass the floats as an XML fragment, which you can take apart in a variety of ways in order to get the individual values out.

正如您所说，您不希望为了读取它的值而对每一行进行重构，因此需要36列。(这也是很好的做法)。但是，您可以将浮点作为XML片段传递，您可以通过各种方式将其分离，以便获得单独的值。

#5

Use this to pass array using "create type table". simple example for user

使用“创建类型表”来传递数组。简单的例子为用户

CREATE TYPE unit_list AS TABLE (
    ItemUnitId int,
    Amount float,
    IsPrimaryUnit bit
);

GO
 CREATE TYPE specification_list AS TABLE (
     ItemSpecificationMasterId int,
    ItemSpecificationMasterValue varchar(255)
);

GO
 declare @units unit_list;
 insert into @units (ItemUnitId, Amount, IsPrimaryUnit) 
  values(12,10.50, false), 120,100.50, false), (1200,500.50, true);

 declare @spec specification_list;
  insert into @spec (ItemSpecificationMasterId,temSpecificationMasterValue) 
   values (12,'test'), (124,'testing value');

 exec sp_add_item "mytests", false, @units, @spec


//Procedure definition
CREATE PROCEDURE sp_add_item
(   
    @Name nvarchar(50),
    @IsProduct bit=false,
    @UnitsArray unit_list READONLY,
    @SpecificationsArray specification_list READONLY
)
AS


BEGIN
    SET NOCOUNT OFF     

    print @Name;
    print @IsProduct;       
    select * from @UnitsArray;
    select * from @SpecificationsArray;
END

#1