Link to home
Start Free TrialLog in
Avatar of Dovberman
DovbermanFlag for United States of America

asked on

Pass Array as Stored Procedure Parameter

I would like to pass an array as a parameter to a stored procedure.
Then I need to parse the passed array.

This is the SP:

ALTER PROCEDURE [dbo].[usp_getEODExtract]
 
@SymbolName     varchar(12) ,
@QuoteDate     datetime ,
@OpenPrice     decimal(8,2) ,
@HighPrice    decimal(8,2) ,
@LowPrice     decimal(8,2) ,
@ClosePrice   decimal(8,2)  

-- @EODArray ??? Datatype
AS
 
BEGIN
   -- SET NOCOUNT ON added to prevent extra result sets from
   -- interfering with SELECT statements.
      SET NOCOUNT ON;

-- FOR each array item
      INSERT INTO StockDataWork
      (SymbolName,QuoteDate,OpenPrice,HighPrice,LowPrice,ClosePrice)
      VALUES(@SymbolName,@QuoteDate,@OpenPrice,@HighPrice,@LowPrice,@ClosePrice)

-- VALUES are from each array item.
 
END

This is the c# code that calls the SP:

    SqlCommand cmd = new SqlCommand("usp_getEODExtract", pconStockSelect);
        cmd.CommandTimeout = 360;
        cmd.CommandType = System.Data.CommandType.StoredProcedure;
        cmd.CommandText = "usp_getEODExtract";

      {
                cmd.Parameters.Clear();
                cmd.Parameters.Add("@SymbolName", SqlDbType.NVarChar).Value = values[0];
                cmd.Parameters.Add("@QuoteDate", SqlDbType.DateTime).Value = dteQuoteDate;
                cmd.Parameters.Add("@OpenPrice", SqlDbType.Decimal).Value = Convert.ToDecimal(values[2]);
                cmd.Parameters.Add("@HighPrice", SqlDbType.Decimal).Value = Convert.ToDecimal(values[3]);
                cmd.Parameters.Add("@LowPrice", SqlDbType.Decimal).Value = Convert.ToDecimal(values[4]);
                cmd.Parameters.Add("@ClosePrice", SqlDbType.Decimal).Value = Convert.ToDecimal(values[5]);
                //cmd.Parameters.Add("@TradeVolume", SqlDbType.BigInt).Value = Convert.ToInt64(values[6]);
                RunProcedure(cmd, "cmdUpdate_Click");
                intRowCount++;

             } // end for (int i = 0; i != values.Length; ++i)

Is this possible?

Thanks,
ASKER CERTIFIED SOLUTION
Avatar of Carl Tawn
Carl Tawn
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Dovberman

ASKER

Is this the Structured data type?

CREATE TYPE dbo.EmployeeList
AS TABLE
(
  EmployeeID INT
);
GO

my @List is a 7 column array and has 1800 rows.

Is this within the resource limits?

CREATE TYPE dbo.EODList
AS TABLE
(
  SymbolName VarChar(12)
  QuoteDate String
+5 more cols

);
GO
>> Is this the Structured data type?
Yes. You are basically defining a table structure that you can use as a parameter to your stored procedure.

I'm not sure if it will be able to implicitly convert an array to the table parameter type for the stored proc. You may have to load the contents of your array into a DataTable first.
This is getting too complicated. My current process works.

Thanks.
Thanks