Stored Procedure Always Taking First Parameter From The Code

I have a stored procedure like this: ALTER PROCEDURE [dbo].[T_TransactionSummary] @locations nvarchar AS BEGIN .............. ............. AND (Location_tbl.Locid IN (@loc

Solution 1:

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

@locationsVARCHAR(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 ASTABLE(Item INTPRIMARY KEY);

Then you use the parameter this way:

@locations dbo.Integers READONLY

And you can say:

WHEREEXISTS (SELECT1FROM@locationsWHERE 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.

Solution 2:

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.