-2

I have a stored procedure like this:

ALTER PROCEDURE [dbo].[T_TransactionSummary]  
 @locations nvarchar
AS
    BEGIN 
..............
.............
AND (Location_tbl.Locid IN (@locations))

my locid field is integer this locid coming from my listbox.if i select one item 1 locid will come.if i select 2 item 2 locid will come.. I've got a ListBox which populates the @locations parameter (an integer), I took my listbox value like this

cnt = LSTlocations.SelectedItems.Count
 Dim list As New List(Of Integer)
        Dim locid As Integer
        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
 Dim da As New SqlDataAdapter
        Dim ds As New DataSet
        Dim cmd23 As New SqlCommand("T_TransactionSummary", con.connect)
        cmd23.CommandType = CommandType.StoredProcedure
        cmd23.Parameters.Add("@locations", SqlDbType.Int).Value = String.Join(",", list)
        da.SelectCommand = cmd23
        da.Fill(ds)

now my locationid from listbox is passing to stored procedure 1,2,3, only . but stored procedure always taking first value(i mean in this case taking 1).

user2878851
  • 153
  • 1
  • 5
  • 17

2 Answers2

7

First, you absolutely need to define a length for your parameter... what you currently have is a string that is being truncated at the first character.

DECLARE @locations NVARCHAR;
SET @locations = '1,2,3';
SELECT @locations;

Result:

1

You need to say

@locations VARCHAR(MAX)

You don't need NVARCHAR to store a comma-separated list of integers. (I assume you may have a long list of integers, but perhaps MAX could be 8000 instead.)

Then, you can't say IN (@locations) - this will not work correctly, either you will get an error message about converting '1,2,3...' to an int, or it just won't find the value - that is comparing to the whole string, not the set. So you could do this with dynamic SQL, e.g.

SET @sql = @sql + ' WHERE locations IN (' + @locations + ') ...;';

But that is fraught with all kinds of other problems, including maintainability and exposure to SQL injection. I highly recommend table-valued parameters instead. Basically you create a type like this:

CREATE TYPE dbo.Integers AS TABLE(Item INT PRIMARY KEY);

Then you use the parameter this way:

@locations dbo.Integers READONLY

And you can say:

WHERE EXISTS (SELECT 1 FROM @locations WHERE Item = Location_tbl.Locid)

In your VB.Net code, you populate your listbox selections into a DataTable (instead of an int or a string), and pass the DataTable as a parameter with SqlDbType.Structured. I have some examples here, but they're C#:

http://www.sqlperformance.com/2012/08/t-sql-queries/splitting-strings-now-with-less-t-sql

There is also plenty of documentation on TVPs on MSDN.

Community
  • 1
  • 1
Aaron Bertrand
  • 272,866
  • 37
  • 466
  • 490
  • sir..what i have to change ..? – user2878851 Oct 21 '13 at 17:00
  • 1
    @user2878851 I think I've given pretty thorough and useful instructions here. – Aaron Bertrand Oct 21 '13 at 17:04
  • 2
    @AaronBertrand: I agree. Your answer looks pretty complete. – NotMe Oct 21 '13 at 17:06
  • sir i executed first:CREATE TYPE dbo.Integers AS TABLE(Item INT PRIMARY KEY); then i added "@locations dbo.Integers READONLY" to my sp.but getting error Must declare the scalar variable "@locations". – user2878851 Oct 21 '13 at 17:15
  • 2
    @user2878851 this isn't a "write all my code for me" forum. Especially when you only included a very brief segment of your original code in the first place. Did you see how I changed the use of the `@locations` variable? Perhaps I should have named it something different? Have you looked at any of the links I provided to use my answer to help you learn how to use these technologies? – Aaron Bertrand Oct 21 '13 at 17:21
1

The following is the first problem.

cmd23.Parameters.Add("@locations", SqlDbType.Int).Value = String.Join(",", list)

You are adding the @locations parameter as an int. When you assign the value to it VB is converting your string to an int. Which means "1,2,3,4" is going to become "1".

Change that to a SqlDbType.VarChar

As to the second, I'm not entirely sure you can do this:

AND (Location_tbl.Locid IN (@locations))

You might want to look into table value parameters instead.

NotMe
  • 87,343
  • 27
  • 171
  • 245
  • @user2878851: Look at Aaron's answer. The part where he tells you to change the sp parameter to `@locations dbo.Integers READONLY` is a TVP. – NotMe Oct 21 '13 at 17:06
  • i just given in my stored procedure like this:@locations integer readonly..now i am getting error:The parameter "@locations" can not be declared READONLY since it is not a table-valued parameter. – user2878851 Oct 21 '13 at 17:11