Solved

Pass Array as Stored Procedure Parameter

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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

A long time ago (May 2011), I have written an article showing you how to create a DLL using Visual Studio 2005 to be hosted in SQL Server 2005. That was valid at that time and it is still valid if you are still using these versions. You can still re…
Entering time in Microsoft Access can be difficult. An input mask often bothers users more than helping them and won't catch all typing errors. This article shows how to create a textbox for 24-hour time input with full validation politely catching …
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
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…

744 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

11 Experts available now in Live!

Get 1:1 Help Now