Passing arrays to stored procedures

Dear all,

i want to know how i can pass multiple values in the form of arrays to a stored procedures.

the technique by which i pass multiple values to a stored procedure beginning along with declarations are as follows:

Dim configurationAppSettings As System.Configuration.AppSettingsReader = New System.Configuration.AppSettingsReader()
 Me.cmdInsSlabHmst = New System.Data.OleDb.OleDbCommand()
 Me.OleDbConnection1 = New System.Data.OleDb.OleDbConnection()
 Me.cmdInsSlabDMst = New System.Data.OleDb.OleDbCommand()
 '
 'cmdInsSlabHmst
 '
 Me.cmdInsSlabHmst.CommandText = "PKGSLABHMST.INSSLABHMST"
 Me.cmdInsSlabHmst.CommandType = System.Data.CommandType.StoredProcedure
 Me.cmdInsSlabHmst.Connection = Me.OleDbConnection1
 Me.cmdInsSlabHmst.Parameters.Add(New System.Data.OleDb.OleDbParameter("iSLABDESC", System.Data.OleDb.OleDbType.VarChar, 50))
 Me.cmdInsSlabHmst.Parameters.Add(New System.Data.OleDb.OleDbParameter("iSLABUNIT", System.Data.OleDb.OleDbType.VarChar, 1))
 Me.cmdInsSlabHmst.Parameters.Add(New System.Data.OleDb.OleDbParameter("iREMARKS", System.Data.OleDb.OleDbType.VarChar))
 Me.cmdInsSlabHmst.Parameters.Add(New System.Data.OleDb.OleDbParameter("iSLABFROM", System.Data.OleDb.OleDbType.VarChar))
 Me.cmdInsSlabHmst.Parameters.Add(New System.Data.OleDb.OleDbParameter("iSLABRATE", System.Data.OleDb.OleDbType.VarChar))
 Me.cmdInsSlabHmst.Parameters.Add(New System.Data.OleDb.OleDbParameter("iNOOFRECORDS", System.Data.OleDb.OleDbType.Integer))
 '
 'OleDbConnection1
 '
 Me.OleDbConnection1.ConnectionString = CType(configurationAppSettings.GetValue("ConnectionString", GetType(System.String)), String)
'Passing multiple values to the procedure with the help of ~ sign
 Dim strCode As String
 Dim i As Integer
 'Dim dblSlabRate As Decimal
 'Dim dblSlabFrom As Decimal
 Dim strSlabRate As String
 Dim strSlabFrom As String
 Dim strSlabRateP As String
 Dim strSlabFromP As String
 Dim intCntr As Integer
 'Me.cmdInsSlabHmst.Parameters("iSLABDESC").Value = txtSlabDesc.Text
 'Me.cmdInsSlabHmst.Parameters("iSLABUNIT").Value = ddlSalbUnit.SelectedItem.Value
 'Me.cmdInsSlabHmst.Parameters("iREMARKS").Value = txtRemarks.Text
 'OleDbConnection1.Open()
 'strCode = cmdInsSlabHmst.ExecuteScalar
 'OleDbConnection1.Close()
 For i = 0 To dgSlabDtl.Items.Count - 1
 If i = dgSlabDtl.Items.Count - 1 Then
 'dblSlabRate = CType(dgSlabDtl.Items(i).FindControl("txtSlabRate"), TextBox).Text
 'dblSlabFrom = CType(dgSlabDtl.Items(i).FindControl("txtSlabFrom"), TextBox).Text
 strSlabRate = CType(dgSlabDtl.Items(i).FindControl("txtSlabRate"), TextBox).Text
 strSlabFrom = CType(dgSlabDtl.Items(i).FindControl("txtSlabFrom"), TextBox).Text
 Else
 'dblSlabRate = CType(dgSlabDtl.Items(i).FindControl("lblSlabRate"), Label).Text
 'dblSlabFrom = CType(dgSlabDtl.Items(i).FindControl("lblSlabFrom"), Label).Text
 strSlabRate = CType(dgSlabDtl.Items(i).FindControl("lblSlabRate"), Label).Text
 strSlabFrom = CType(dgSlabDtl.Items(i).FindControl("lblSlabFrom"), Label).Text
 End If
 strSlabRateP += strSlabRate & "~"
 strSlabFromP += strSlabFrom & "~"
 intCntr += 1
 'If dblSlabRate <> "" And dblSlabFrom <> "" Then
 'InsDtl(strCode, dblSlabFrom, dblSlabRate)
 'End If
 Next
 If strSlabRateP <> "" And strSlabFrom <> "" Then
 With cmdInsSlabHmst
 .Parameters("iSLABDESC").Value = UCase(txtSlabDesc.Text)
 .Parameters("iSLABUNIT").Value = ddlSalbUnit.SelectedItem.Value
 .Parameters("iREMARKS").Value = txtRemarks.Text
 .Parameters("iSLABFROM").Value = strSlabFromP
 .Parameters("iSLABRATE").Value = strSlabRateP
 .Parameters("iNOOFRECORDS").Value = intCntr
 End With
 OleDbConnection1.Open()
 cmdInsSlabHmst.ExecuteNonQuery()
 OleDbConnection1.Close()
 End If

to the insert procedure i am passing multiple values with the help of ~ sign and in the procedure the individual values are separated by identifying the position of ~ sign and the no. of records which have been passed. For which a complicated stored procedure has been written.

i want to pass multiple values in an array, so that my stored procedure becomes simple and runs faster. So, if someone tells me how to pass arrays to a stored procedure (with code example), it will be of real help.

regards
subhajitWell, what you are doing is probably the way to go. I don't understand though, how you can say a 'complicated stored procedure' was written to chop up a ~ delimited list. It should be only a couple of very simple statements to do.

I do the same thing quite often, for example if I have a checkboxlist and I want to pass a list of all items that the user checked to a stored proc, I go through the list in my ASP code and create a string in much the same way you do. Then in my stored proc I do something like:

while strpos(@.List, '~') > 0
begin
 insert into #Table values (substring(@.List, 1, strpos(@.List, '~') - 1))
 set @.List = substring(@.List, strpos(@.List, '~') + 1, length(@.List))
end

which is easy enough for me, and executes in a flash, even for hundreds of items.

If you reach the limit of this code (@.List close to 8000 chars) you need to do something else. In this case it can be useful to consider your specific application. Where are those values coming from? Are you storing it in bits and pieces to the array, and then in one shot you want to write it to your DB? If so you should think about writing the values to your DB as they become available - instead of keeping them in an array in server memory.

But from your code it looks like you're just doing the same sort of thing I do, so I don't see the problem.

BTW, your code is very difficult to look at, since 1/2 of it is commented out. If you can distill it into just what we need to see your problem, it makes it easier for us to help you. It will also increase the likelyhood of an accurate answer.sql