Convert nvarchar to int

Question

I want to write a query that select some records like below query but @reseller is of type nvarchar and an error occurs that it can't convert nvarchar to int. How can I solve it?

select * from Factor where code in (@reseller)

@reseller is '1,2,3'

Answer 1

Use this function, which splits the strings and returns the intvalue in table.

Create Function dbo.CsvToInt ( @Array varchar(1000)) 
returns @IntTable table (IntValue int) 
AS 
Begin 
  Declare @separator char(1) 
  Set @separator = ',' 
  Declare @separator_position int 
  Declare @array_value varchar(1000)
  Set @array = @array + ',' 
  While patindex('%,%' , @array) <> 0 
  Begin 
     SELECT @separator_position = patindex('%,%' , @array) 
     SELECT @array_value = left(@array, @separator_position - 1) 
     Insert @IntTable Values (Cast(@array_value as int)) 
     SELECT @array = stuff(@array, 1, @separator_position, '') 
  End 
Return 
End 

One of the great things about user-defined functions is that they can be used in a FROM clause. We can use this function like this:

SELECT *
From dbo.CsvToInt('1,5,11')

which returns

IntValue    
----------- 
1
5
11

In your case you can us like

SELECT * 
FROM Factor 
WHERE code IN dbo.CsvToInt(@reseller)

Take a look at here

Answer 2

You have to SELECT @result and use CAST aggregate function to cast it to INT like this‌:

select * from Factor where code in (SELECT‌ CAST(@reseller AS‌ INT))

Fore more information about CAST aggregate function follow this : msdn.microsoft.com/en-us/library/ms187928.aspx