Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
toddhd
Yak Posting Veteran
55 Posts |
 Posted - 2005-05-06 : 14:17:01
|
| I have a very simple table that looks like this:ProductID int (PK-Identity)Product varchar(50)I also have a SPROC that looks like this:CREATE PROCEDURE dbo.ListProducts @ProductID varchar(1000)ASSELECT [ProductID], [Product]FROM ProductsWHERE ProductID IN (@ProductID)GONow, the reason I have that @ProductID setup as a varchar as opposed to an int is because I want to be able to pull up a list of all products within a given list of product ID's. SELECT * FROM Products WHERE ProductID IN (12,13,14)So far, so good, but the problem comes in when I try to pass that string of integers from .NET to the SPROC. I define the string in my program and pass it as a parameter to the SPROC. So in the program, it might look like this (not really, but you get the idea):Dim s As String = "12,13,14"When it gets to the SPROC however, SQL is adding single quotes around it. So what I end up with is this:SELECT * FROM Products WHERE ProductID IN ('12,13,14')Well, that doesn't work. Ok, so I just add some single quotes in between the numbers, like this:Dim s As String = "12','13','14"But when that gets passed to SQL, it now looks like this:SELECT * FROM Products WHERE ProductID IN ('12'',''13'',''14')(I'm finding this out through the Profiler tool.)I have NO IDEA where all these extra quotes are coming from. This is just silly. How the heck do I pass a string of integers without all the extra malarky? |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2005-05-06 : 16:30:48
|
| http://www.sqlteam.com/item.asp?ItemID=11499Be sure to check out the comment section for the set-based solution.Tara |
 |
|
|
|
|
|
|
|
Passing a string of integers to a SPROC