-1

i need to update some rows where there BF_ID is in an nvarchar @ArrBF ='1,3,5,7'

update BILL_FOLDERS_DATA
SET
    BFD_Quantity = @CFD_Quantity
WHERE BFD_Path = @CFD_Path AND BF_ID IN (@ArrBF)

i get an error because i need to convert the array to integers. how can i achieve it?

eyalb
  • 2,994
  • 9
  • 43
  • 64
  • http://www.codeproject.com/Tips/584680/Using-comma-separated-value-parameter-strings-in-S – Tim Schmelter Sep 07 '15 at 12:37
  • 1
    definite duplicate of well known issue. here @ArrBF is just one string & it does not matter if it looks like a set of integers, it is still a string. Have to split that string into rows, and in your case treat then as integers also it appears. – Paul Maxwell Sep 07 '15 at 12:40

1 Answers1

1

The easiest way would be to convert the array into a table valued function, and using the result in a join eg - 

create FUNCTION [dbo].[F_SplitString] 
( @String NVARCHAR(max),@Delimiter VARCHAR(5)) 

RETURNS @SplittedValues TABLE 

( OccurenceId SMALLINT IDENTITY(1,1), SplitValue VARCHAR(MAX) ) 

  AS 
  begin
  INSERT INTO @SplittedValues 
  SELECT Split.a.value('.', 'VARCHAR(100)') AS Data  
 FROM  
 (SELECT CAST ('<M>' + REPLACE(@String , @Delimiter, '</M><M>') + '</M>' AS XML) AS Data ) AS A 
 CROSS APPLY Data.nodes ('/M') AS Split(a)

RETURN 
 END
SeanR
  • 196
  • 1
  • 11
  • It really isn't necessary to do things like that with joins anymore - at least on MS SQL. Just use `in (select ...)`. I know, old habits die hard, but it's worth it. – Luaan Sep 07 '15 at 12:42
  • Joins are far more efficient than a sub-select in the where clause - if you have many records in the set, then it will have to reevaluate the sub-select for every single row... – SeanR Sep 07 '15 at 13:11
  • That used to be true; it rarely is nowadays. Have a look at the execution plan of the query - it will usually be exactly the same. – Luaan Sep 07 '15 at 14:11