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,
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