paramter for 'where ... in ... '

When I hardcode a value for the following code, it works, but when I try to pass a paramter of the same value is doesn't return any rows:

ALTER PROCEDURE GetVendorsByList
(
@VendorList varchar(10)
)
AS
SELECT VendorID
FROM Vendors
WHERE VendorID in (' & @VendorList & ')

I eventually want to be able to pass in a list like '1','2','3', but to test I am only passing in '2'.

This is probably a stupid error, what am I doing incorrectly?

Are you supposed to have quotes around your parsed variable when use stored procedures? (I've just started using them, so am not all that cluey.)

Also, I'm being picky, but why is your ID field a varchar? Should be an int

I started out with the ID field as int, but realized that a list of vendors such as '1234567', '1234568', '1234569',.....'123456666', doesn't fit into in int field.

The problem is that just 2 or '2' doesn't work, let alone a list.

Stumpy's right. What you're doing right now is asking for all the records that compare to the literal string (" & @VendorList & "). To do this the way you want to, I think you would need to build an SQL string dynamically and execute it.



You would call it like:



I would think about taking a second look at your database structure and programming logic that leads you to a situation where you have a collection of VendorIDs and no idea which ones are valid.

hmm.. how is the sql statement with the 'where ... in ..' that you have:
SELECT VendorID
FROM [Vendors]
WHERE [VendorID] IN ( ' + @array + ')'

different from the one I have:

SELECT VendorID
FROM Vendors
WHERE VendorID in (' & @VendorList & ')

The only difference I see the the brackets surrounding the table name.

What I am doing prior to this call is building a list of vendors that a customer wants to process later. So I am storing a collection of vendorIDs, and in this call I want to retrieve data from each vendor in the collection. Since this is web-based I do not want to store all the data from each vendor while they are selecting vendors, only after all are selected.

How would you go about doing this since it seems my approach is not a good one?

Give it another look. It's building the SQL into a string that includes the array you specify that is then executed. This works on my database.

As for the data structure, that does sound like a reasonable way to accomplish it.

ah, I see. what does 'sp_executeSQL' refer to? I am using SQLServer if that makes a difference.

Also, I have only done rudimentary stored procedures, do you know any online sites that go deeper into stored procedures than the basic tutorials?

Did it work?

SQL Server has a series prewritten stored procedures called "system" stored procedures that you can call in your own programming--they're kind of like built in functions in ASP.

'sp_executeSQL' just takes a string and executes it as SQL.

The problem with your Stored Procedure wasn't anything wrong with the programming; it was in the way SQL Server evaluates Stored Procedure parameters. SQL Server always evaluates them as literal strings. So if you want SQL Server to look at part of the parameter as a literal string and part of it as a command (like the comma separating values) you have to build the string by hand and then execute it.

This is a great book on Transact-SQL:
http://www.amazon.com/exec/obidos/t...=books&n=507846

Does anyone know of any good more indepth online tutorials?

numbernine you are the man! It worked fine. And I ordered the book

numbernine, I was using the expression 'you are the man' in its genderless meaning in case you are female. Should have checked first before using that one.

LOL--Very sensitive-minded of you to catch that, but yes I am a man...