?
Solved

Pass Array as Stored Procedure Parameter

Posted on 2014-01-02
5
Medium Priority
?
559 Views
Last Modified: 2014-01-02
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,
0
Comment
Question by:Dovberman
  • 3
  • 2
5 Comments
 
LVL 52

Accepted Solution

by:
Carl Tawn earned 2000 total points
ID: 39751024
If you are using SQL 2008 then you could do it by using a Structured data type, although that would require you to create a corresponding UDT in SQL Server to map it to.

Sample here if you want to go that route: http://stackoverflow.com/questions/11102358/how-to-pass-an-array-into-a-sql-server-stored-procedure

Otherwise, you will need to convert the array contents to a delimited list and pass it to the procedure, and have the procedure parse it out.
0
 

Author Comment

by:Dovberman
ID: 39751051
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
0
 
LVL 52

Expert Comment

by:Carl Tawn
ID: 39751065
>> 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.
0
 

Author Comment

by:Dovberman
ID: 39751125
This is getting too complicated. My current process works.

Thanks.
0
 

Author Closing Comment

by:Dovberman
ID: 39751126
Thanks
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

ASP.Net to Oracle Connectivity Recently I had to develop an ASP.NET application connecting to an Oracle database.As I am doing it first time ,I had to solve several problems. This article will help to such developers  to develop an ASP.NET client…
The article shows the basic steps of integrating an HTML theme template into an ASP.NET MVC project
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlin…
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question