pass multiple integer variable same time to the integer parameter

Question

I've got a stored procedure with the following criteria:
WHERE (Transaction_tbl.dtime BETWEEN @fromDate AND @toDate) AND (Location_tbl.Locid IN (@locations)) I've got a ListBox which populates the @locations parameter (an integer), and two DateTimePicker controls for the @fromDate and @toDate.
I took my listbox value like this:

 cnt = LSTlocations.SelectedItems.Count
      If cnt > 0 Then
            For i = 0 To cnt - 1
                Dim locationanme As String = LSTlocations.SelectedItems(i).ToString
                locid = RecordID("Locid", "Location_tbl", "LocName", locationanme)
                list.Add(locid)
            Next
            End If

i want to pass this list item value to my stored procedure...how i can do this?

cmd23.Parameters.Add("@startDate", SqlDbType.NVarChar, 50, ParameterDirection.Input).Value= startdate
cmd23.Parameters.Add("@endDate", SqlDbType.NVarChar, 50, ParameterDirection.Input).Value = enddate
cmd23.Parameters.Add("@locations", SqlDbType.Int) ' <= ???

How can this code be modified to pass several integer identifiers as the @locations parameter so that I may select several items in my listbox?

Possible duplicate of http://stackoverflow.com/questions/2377506/pass-array-parameter-in-sqlcommand. That post is for C#, but I'm guessing it's basically the same for VB. — musical_coder, Oct 20 '13 at 05:54
I think it does not matter that you have to pass a integer, as the value is an object. See: http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlparametercollection.addwithvalue.aspx — Martao, Oct 20 '13 at 10:41

score 0 · Answer 1 · edited May 23 '17 at 10:25

You could try concatenating your selected integers into a comma-separated string, then pass that into your Stored Procedure.

You can then use this great SO post that will convert that CSV string into a TABLE for you.

You can then use that table variable with an INNER JOIN to your "Location_tbl.Locid"

    @Locations VARCHAR(MAX) --Your inbound CSV list of LocID's
    .
    .
    .
    DECLARE @MyTable TABLE
       (LocID VARCHAR(50))

    INSERT INTO @MyTable
       (LocID)
    SELECT dbo.Split(@Locations, ',') --This is the function from the aforementioned POST
    .
    .
    SELECT * FROM [your list of tables]
    INNER JOIN @MyTable L ON Location_tbl.Locid = L.LocID
    WHERE (Transaction_tbl.dtime BETWEEN @fromDate AND @toDate)

pass multiple integer variable same time to the integer parameter

1 Answers1