1

This works:

declare @ids varchar(50) = '1, 2, 3'
create table #test  (id int)
exec('insert into #test select userid from users where userid in(' + @ids + ')')

This doesn't:

declare @ids varchar(50) = '1, 2, 3'
create table #test  (id int)
insert into #test select userid from users where userid in(@ids)

It (kinda obviously) returns:

Conversion failed when converting the varchar value '1, 2, 3' to data type int.

Is there an elegant way to make the query work without using exec(), like I try to do in the second piece of code?

Marc.2377
  • 7,807
  • 7
  • 51
  • 95
  • 1
    There are multiple ways, One is to use Dynamic-SQL like [here](http://stackoverflow.com/a/33010765/5070879), or just split string. – Lukasz Szozda Oct 10 '15 at 19:09
  • error are coming because `@ids` is nvarcahr and `userid`is integer – wiretext Oct 10 '15 at 19:12
  • http://stackoverflow.com/questions/2591856/assign-a-list-of-integers-to-an-var/2592071#2592071. Is this solution – adopilot Oct 10 '15 at 19:46
  • This is rather ugly and non-sargable: `WHERE CONCAT(',', REPLACE(@ids, ' ', ''), ',') LIKE concat('%,', userid, ',%') ` – Giorgos Betsos Oct 10 '15 at 20:04
  • 1
    There is no elegant solution at level you are examining the problem. The elegant solution is to step back in your design and not put yourself in this position in the first place. Instead, you want to be able construct a `select` query that describes the values for your `IN()` clause. This means looking at how those lists are first generated, and finding a different way to get that data to sql server. – Joel Coehoorn Oct 10 '15 at 20:08
  • 1
    If you're staying with a string variable there is no elegant way. You could split the @ids into a temp table, and join to that table in the select. – Amir Pelled Oct 11 '15 at 08:33

0 Answers0