0

I am creating a stored proc for SQL Server. The WHERE clause of my select statement contains an IN statement. I want to pass a series of integer IDs into the stored procedure, to be used with the IN operator. So essentially the SQL will be...

SELECT * From Customers Where CustomerId IN (1,2,3,4,5);

So, how do I pass the 1 thru 5 to the stored proc? Thanks!

WebDevGuy2
  • 1,159
  • 1
  • 19
  • 40
  • 1
    you could use a table-valued parameter – ADyson Apr 11 '19 at 13:44
  • 1
    There are several duplicates for this. You can't pass a "list of values" parameter so a table-valued parameter is the only option that doesn't involve string concatenation. This has its own implications as the server can't know that the contents will be when it creates the execution plan and so assumes the TVP will only contain 1 item – Panagiotis Kanavos Apr 11 '19 at 13:45
  • Pass it as an xml, then do a `CustomerId IN (SELECT a.value('Id[1]', 'INT') AS [Id] FROM @xml.nodes('/root/Ids/Id')) as x(a)` – Ryan Wilson Apr 11 '19 at 13:45
  • 1
    @RyanWilson that's not easier than a TVP – Panagiotis Kanavos Apr 11 '19 at 13:46
  • In SQL Server 2016 it's possible to pass a comma-separated string without resorting to string concatenation, by using `STRING_SPLIT` with eg `Customers cross apply L. That won't improve performance though, nor avoid conversion errors. If that string contains a letter when you expect an integer, you'll get errors – Panagiotis Kanavos Apr 11 '19 at 13:50
  • @PanagiotisKanavos It may not be easier, but still a viable solution. – Ryan Wilson Apr 11 '19 at 13:55
  • @ADyson can you post an example then I can mark it as the answer? Thanks! – WebDevGuy2 Apr 11 '19 at 14:05
  • @WebDevGuy2 instead of marking an answer, you should delete this question and upvote this answer: https://stackoverflow.com/a/1525153/1507566 – Tab Alleman Apr 11 '19 at 14:27
  • @WebDevGuy2 Tab is correct. And questions marked as duplicates cannot have answers added to them anyway, but thanks. – ADyson Apr 11 '19 at 14:38

0 Answers0