using charindex in sql query

Question

hi, I have a string named itemIDs separated by commas(12,43,34,..) and in order to use it as a parameter i need to convert it to int since the itemID in the db is in int format.

this is what i wrote but i get an error saying Incorrect syntax near the keyword 'as'.

using (SqlCommand searchResult = new SqlCommand("SELECT ItemID, Name, RelDate, Price, Status FROM item_k WHERE (itemID = cast(charindex(',',(@itemIDs as int))))", searchCon))

I cant figure it out, what seems to be the issue here?

thank you

Answer 1

Where are you getting itemIDs from? If it's a string you generate mathematicaly, and is not from an outside source you can use:

... "SELECT ItemID, Name, RelDate, Price, Status FROM item_k WHERE itemID IN (" + itemIDs + ")"

Answer 2

I would suggest a different approach here for your WHERE clause. You can use IN to specify your list.

using (SqlCommand searchResult = new SqlCommand("
SELECT ItemID, Name, RelDate, Price, Status 
FROM item_k 
WHERE itemID IN (" + itemIDs + ")"

That corresponds to SQL like this:

SELECT ItemID, Name, RelDate, Price, Status 
FROM item_k 
WHERE itemID IN (12,43,34)