Dovberman
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,Open Price,High Price,LowP rice,Close Price)
VALUES(@SymbolName,@QuoteD ate,@OpenP rice,@High Price,@Low Price,@Clo sePrice)
-- VALUES are from each array item.
END
This is the c# code that calls the SP:
SqlCommand cmd = new SqlCommand("usp_getEODExtr act", pconStockSelect);
cmd.CommandTimeout = 360;
cmd.CommandType = System.Data.CommandType.St oredProced ure;
cmd.CommandText = "usp_getEODExtract";
{
cmd.Parameters.Clear();
cmd.Parameters.Add("@Symbo lName", SqlDbType.NVarChar).Value = values[0];
cmd.Parameters.Add("@Quote Date", SqlDbType.DateTime).Value = dteQuoteDate;
cmd.Parameters.Add("@OpenP rice", SqlDbType.Decimal).Value = Convert.ToDecimal(values[2 ]);
cmd.Parameters.Add("@HighP rice", SqlDbType.Decimal).Value = Convert.ToDecimal(values[3 ]);
cmd.Parameters.Add("@LowPr ice", SqlDbType.Decimal).Value = Convert.ToDecimal(values[4 ]);
cmd.Parameters.Add("@Close Price", SqlDbType.Decimal).Value = Convert.ToDecimal(values[5 ]);
//cmd.Parameters.Add("@Tra deVolume", 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,
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,Open
VALUES(@SymbolName,@QuoteD
-- VALUES are from each array item.
END
This is the c# code that calls the SP:
SqlCommand cmd = new SqlCommand("usp_getEODExtr
cmd.CommandTimeout = 360;
cmd.CommandType = System.Data.CommandType.St
cmd.CommandText = "usp_getEODExtract";
{
cmd.Parameters.Clear();
cmd.Parameters.Add("@Symbo
cmd.Parameters.Add("@Quote
cmd.Parameters.Add("@OpenP
cmd.Parameters.Add("@HighP
cmd.Parameters.Add("@LowPr
cmd.Parameters.Add("@Close
//cmd.Parameters.Add("@Tra
RunProcedure(cmd, "cmdUpdate_Click");
intRowCount++;
} // end for (int i = 0; i != values.Length; ++i)
Is this possible?
Thanks,
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
>> 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.
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.
ASKER
This is getting too complicated. My current process works.
Thanks.
Thanks.
ASKER
Thanks
ASKER
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