casting values SQL IN function

I have this problem where I am passing NVARCHAR parameter to a stored procedure, and the field type in the table for that parameter is INT.

I am doing tha开发者_JAVA百科t because I am using IN function, and for example if I want to send multiple parameters, that is the only possible way to do through .NET. Or not? The SP doesn't get executed because of the wrong type, and I am trying to cast the value, but I get an error. Here is how I am trying: Example:

@Parameter nvarchar OrderStatusID = ('30, 40')

(o.OrderStatusID IN (CAST(@OrderStatusID as int)) OR @OrderStatusID IS NULL)

Is there a way to cast each value separately, or what will be the best way to cast whole value as INT.

The best way to do this is convert the input parameters to a table, then use the in statement on that table.

You can do it with a CTE for example

WITH CTE ( pos, pos_begin, pos_end ) AS 
(
  SELECT 0, 1, CHARINDEX( ',', @p + ',' ) 
  UNION ALL
  SELECT pos + 1, pos_end + 1, CHARINDEX( ',', @p + ',', pos_end + 1 ) 
  FROM CTE
  WHERE CHARINDEX( ',', @p + ',', pos_end + 1 ) > 0 
), numList as
(
  SELECT SUBSTRING( @p, pos_begin , pos_end - pos_begin ) AS "value"
  FROM CTE
)
SELECT -- whatever you want
WHERE (o.OrderStatusID IN (SELECT CAST(value AS int) FROM numList)) OR @p is NULL

There are other ways to do this, read about many other techniques here -- where this was adapted from. http://www.projectdmx.com/tsql/sqlarrays.aspx#Cte

(NB, since I adapted this quick from the example page I did not optimize it, it is clear the CTE could be made simpler and thus faster.)

@Parameter nvarchar OrderStatusID = ('30, 40')

(o.OrderStatusID IN (CAST(@OrderStatusID as int)) OR @OrderStatusID IS NULL)

I typically do something like this

SET @OrderStatusId = ','+@OrderStatusID+','

CharIndex(','+trim(str(o.OrderStatus))+',' , @OrderStatusId)  >0

But this is not very fast, using functions in a WHERE clause impacts performance.