1

I hope this is not too bad of a logic that I am after. I have a web page which has a list/select box. A user may select one or many of the items from the list. This process sends each items database id to the server in following format

String listOfIdees= '12,14,54,87'

I pass this cs list to stored procedure..where I want to check if none of these records already exist or exist using following format

SELECT * FROM mytable WHERE recId in (@listOfIdees)

This of course throws an error that 

Conversion failed when converting the varchar value '12,14,54,87' to data type int.

If user selects one record, that is converted to int correctly but when there is a comma separated list then there is exception

Can some kind soul guide that what is the best approach to check records existing in such manner as I don't think I can pass an array of int from my web application to stored procedure?

Cœur
  • 37,241
  • 25
  • 195
  • 267
user1063108
  • 662
  • 1
  • 10
  • 24
  • 2
    The SQL Server expert and MVP Erland Sommarskog has written extensively about this for example in this article: http://www.sommarskog.se/arrays-in-sql-2008.html It should provide all information you need. – jpw Sep 11 '15 at 23:42
  • Also, this has been asked and answered several times before. – jpw Sep 11 '15 at 23:43
  • my apologies I did do a search but I guess my search query was not correct...thanks for your help regardless – user1063108 Sep 11 '15 at 23:45
  • No worries, it can be hard to find the appropriate earlier answers. – jpw Sep 11 '15 at 23:54

2 Answers2

0

This is a way to resolve this:

Just paste this into an empty query window and execute. Adapt to your needs...

DECLARE @tbl TABLE(ID INT, SomeValue VARCHAR(10));
INSERT INTO @tbl VALUES
 (1,'value 1')
,(2,'value 2')
,(3,'value 3')
,(4,'value 4')
,(5,'value 5');

DECLARE @ListOfIDs VARCHAR(20)='1,3,5';

WITH ListOfIDs AS
(
    SELECT CAST('<root><r>'+REPLACE(@ListOfIDs,',','</r><r>')+'</r></root>' AS XML) AS IDsAsXML
)
,ListOfIDsResolved AS
(
    SELECT x.y.value('.','int') AS ID
    FROM ListOfIDs
    CROSS APPLY ListOfIDs.IDsAsXML.nodes('/root/r') x(y)
)
SELECT * FROM @tbl AS tbl
INNER JOIN ListOfIDsResolved ON tbl.ID=ListOfIDsResolved.ID
Shnugo
  • 66,100
  • 9
  • 53
  • 114
0

The simplest is to use CHARINDEX like this:

SELECT * FROM mytable 
 WHERE CHARINDEX(
    ',' + CAST(Id AS NVARCHAR(50)) + ',', 
    ',' + @listOfIdees + ','
    ) > 0
Aram
  • 5,537
  • 2
  • 30
  • 41
  • simple, but slow... And you should be sure, that there are no blanks in the given ID string :-) – Shnugo Sep 12 '15 at 00:12