CODEWITHSUNDEEP
c#sql-server
Shell
Shell

Reputation: 6849

How to pass multiple records in stored procedure parameter?

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

Upvotes: 1

Views: 7790

Answers (3)

sam yi
sam yi

Reputation: 4934

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).

Upvotes: 3

G. Stoynev
G. Stoynev

Reputation: 7791

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.

Upvotes: 3

Satheesh Variath
Satheesh Variath

Reputation: 680

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

Upvotes: 0

Related Questions