0

I need to convert string to integers, but I'm getting a type error.

declare @stringinteger nvarchar(255) null;
set @stringinteger='1,2,3,4'

select *
from user
where id in (@stringinteger)

The error I get:

Conversion failed when converting the nvarchar value '1,2,3,4' to data type int

Murat Can OĞUZHAN
  • 447
  • 7
  • 16
  • 2
    Possible duplicate of [Parameterize an SQL IN clause](https://stackoverflow.com/questions/337704/parameterize-an-sql-in-clause) – Jeroen Mostert Feb 14 '18 at 11:34
  • Possible duplicate of [Passing a varchar full of comma delimited values to a SQL Server IN function](https://stackoverflow.com/questions/878833/passing-a-varchar-full-of-comma-delimited-values-to-a-sql-server-in-function) – eztam Feb 14 '18 at 11:36
  • [Bad habits to kick : declaring VARCHAR without (length)](http://sqlblog.com/blogs/aaron_bertrand/archive/2009/10/09/bad-habits-to-kick-declaring-varchar-without-length.aspx) - you should **always** provide a length for any `varchar` variables and parameters that you use. Right now, your `@stringinteger` will be **exactly 1 character** long.... – marc_s Feb 14 '18 at 12:23
  • @marc_s you are right,but this is just example that for this reason i am writed just nvarchar – Murat Can OĞUZHAN Feb 14 '18 at 13:11

1 Answers1

3

You have two methods to handle this, dynamic SQL and splitting the string.

For the latter, you can use string_split() (introduced in SQL Server 2016) or a similar function (they are all over the web, google "string split sql server"):

select *
from user
where id in (select cast(value as int) from string_split(@stringinteger, ',')) ;

The dynamic SQL looks like:

declare @stringinteger nvarchar(255) null;
set @stringinteger = '1,2,3,4';

declare @sql nvarchar(max);
set 'select *
from user
where id in (@stringinteger)';

set @sql = replace(@sql, '@stringinteger', @stringinteger);

exec sp_executesql @sql;

Note that in SQL Server, you should always provide a length for character types. If you leave it out, then the default varies by context -- and your code may not do what you expect.

Gordon Linoff
  • 1,122,135
  • 50
  • 484
  • 624
  • i cant use string_split because db cant support string_split. @Gordon Linoff and i tried to set – Murat Can OĞUZHAN Feb 14 '18 at 11:36
  • Have a concern with the latter, Gordon. You could easily inject into that without validation of `@stringinteger` prior to the `REPLACE`. Imagine if I passed the value `'1,2,3); DROP TABLE user;--'` For the value. – Larnu Feb 14 '18 at 11:38
  • 2
    @MuratCanOĞUZHAN there are plenty of other options out there for String Splitters, as Gordan says. Have a Google of "T-SQL String Splitter". – Larnu Feb 14 '18 at 11:43