How to pass multiple records in stored procedure parameter?

Question

I have created a stored procedure sp_UpdateRate in Sql Server 2005 database and in my C# application i have a decimal array containing multiple rates. Now, i want to pass that array into my stored procedure. the length of array is not static. How do i do this. Is there any array type in sql parameters? Here is my code:

decimal[] rates = new decimal[lst.Items.Count]; //lst is the ListBox control that containing list of rate.
for (int i=0;i<lst.Items.Count;i++)
{
    rates[i]=Convert.ToDecimal(lst.Items[i]);
}

SqlCommand cmd = Cnn.CreateCommand;  //Cnn is the SqlConnection class object
cmd.CommandText = "sp_UpdateRate";
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.ExecuteNonQuery();

My stored procedure is here

Alter Procedure sp_UpdateRate(@rate decimal(9,2))
AS
BEGIN
     --I want to Update my rates here.
     Update tblRate SET LastUpdate=GetDate(), NewRate=(@rate * 1.658) Where rate=@rate
END

if you are using sql server 2008 then you can use table valued parameter.or you can concatenate the value in string and pass it as varchar.then in proc you can split back and use it.or you can use xml. — KumarHarsh, Feb 28 '14 at 06:38
@KumarHarsh is correct. Lots of suggestions of how to do this in [this question](http://stackoverflow.com/questions/7097079/c-sharp-sql-server-passing-a-list-to-a-stored-procedure) — crthompson, Feb 28 '14 at 06:39
@KumarHarsh what datatype will be if i split string into array in stored procedure? — Shell, Feb 28 '14 at 06:47
you pass string in proc it is varchar.then varchar can be splited back to decimal.you can refer this,http://stackoverflow.com/questions/2647/split-string-in-sql there are several.choose which suit you now and future. — KumarHarsh, Feb 28 '14 at 06:57

score 3 · Answer 1 · answered Feb 28 '14 at 06:43

If you were on a later version, you would have used Table Value Parameters. But that was not available in 2005. There is however a trick that did the work, where you "host" your stored procedure code inside a trigger, and the inserted table plays the role of the table parameter.

Now, you always have the option of passing a coma-delimited string and parsing it into a table inside your stored procedure.

Here is a reference to the earlier one.

score 3 · Accepted Answer · edited May 23 '17 at 12:10

Starting with SQL Server 2008, you can pass table variables...

http://www.techrepublic.com/blog/the-enterprise-cloud/passing-table-valued-parameters-in-sql-server-2008/

Unfortunately for you, you're still on SQL 2005. I would suggest, if a case can be made, to upgrade to SQL Server 2008. If that is not an option, you can use XML datatype.

Problem passing XML Parameter to SQL Server Stored Procedure

Hope that helps... without xml datatypes... the good old SQL 2000 days... you had to do this... http://www.codeproject.com/Articles/7485/Inserting-XML-formatted-data-into-SQL-Server-2000

Even before that... you could have used CSV (string) then used a Split function (found here).

score 0 · Answer 3 · answered Feb 28 '14 at 06:40

0

There is no "Array" in SQL, what we have is table. You can use Table Value Parameters to pass multiple values to stored procedure

Here is a article on how we can do that

C# and Table Value Parameters

Read about Table value parameters here Table-Valued Parameters

answered Feb 28 '14 at 06:40

Satheesh Variath

680
4
10

Is it possible in SQL Server 2005? – Shell Feb 28 '14 at 06:43
No, it's not. Starts in v.2008 – G. Stoynev Feb 28 '14 at 06:44
I guess it depends on the scenario but I've recently implemented a solution using table-value parameters but am not sure which I prefer... TVP or XML. TVP adds another level of dependency on TYPE that I was not too comfortable with. Unless you're passing a large volume of data, I think I would prefer xml datatype. I may be a contrarian in that regard. – sam yi Feb 28 '14 at 07:10

How to pass multiple records in stored procedure parameter?

3 Answers3