Solved

Pass Array as Stored Procedure Parameter

Posted on 2014-01-02
5
512 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 500 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Problem Hi all,    While many today have fast Internet connection, there are many still who do not, or are connecting through devices with a slower connect, so light web pages and fast load times are still popular.    If your ASP.NET page …
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
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…
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.

947 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now