How to pass an array of floats to SQL Server stored procedure?

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?

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 proce开发者_C百科dure.

So, what does the conventional wisdom of StackOverflow recommend?

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.

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

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.

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.

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

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

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.

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.)

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.

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