?
Solved

Pass Array as Stored Procedure Parameter

Posted on 2014-01-02
5
Medium Priority
?
547 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
This article shows how to deploy dynamic backgrounds to computers depending on the aspect ratio of display
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…
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…

771 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