CODEWITHSUNDEEP
asp.netsqlsql-servert-sqlstored-procedures
Laziale
Laziale

Reputation: 8225

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 that 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.

Upvotes: 0

Views: 231

Answers (2)

Sparky
Sparky

Reputation: 15105

@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.

Upvotes: 0

Hogan
Hogan

Reputation: 70538

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.)

Upvotes: 1

Related Questions