Have Geek Brain

Using charindex in sql query

I have a string called itemID separated by commas (12,43,34, ..) and for use as a parameter I need to convert it to int since itemID in db is in int format.

here is what i wrote but i get error 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 can't figure out what seems to be the problem here?

+1


a source to share


2 answers


Where do you get the ItemID from? If this is a string that you are generating mathematically and not from an external source, you can use:



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


a source


I would suggest a different approach 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 + ")"


This corresponds to SQL as follows:

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


a source






More articles:


All Articles