Editing a string for it to be passed to a stored procedure

Question

I have the following string that I'm passing to a stored procedure which is then used as part of the where clause.

WHERE order IN @passedOrders

The string is shown below:

1234, 12345, 123456

In order for my where statement to work I need each of these numbers to be seen as a seperate value so I need the string to read as follows:

'1234', '12345', '123456'

What would be the best way in editing the string for the extra characters to be added in using C#?

`' + yourString.Replace(", ", "', '") + '` should work (Add the first single quote, replace all coma + space by single quote + coma + space + single quote and then add the final single quote) — nalka, Jan 08 '20 at 12:38
Have a look at this: https://stackoverflow.com/a/9384566/8126362 — Johnathan Barclay, Jan 08 '20 at 12:41
I would convert to integer before comparing. Putting commas in I think is a kludge but I have done things like this before. Just putting a space would also work where each digit is a single number. — jdweng, Jan 08 '20 at 12:43

Miguel Peixoto · Answer 1 · 2020-01-08T15:06:10.890

If you're using a SQL Server version prior to 2016 (130), string_split function is not available.

If that's the case, I would suggest an approach similar to this one: stackoverflow.com/questions/29489036/split-comma-separated-varchar-parameter-up-into-temp-table

Create a function like this one:

CREATE FUNCTION [SplitIntegers]
(
   @List      VARCHAR(MAX),
   @Delimiter CHAR(1)
)
RETURNS TABLE
AS
RETURN 
(     SELECT [Item] = CONVERT(INT, [Item]) 
      FROM
      ( 
        SELECT [Item] = x.i.value('(./text())[1]', 'VARCHAR(max)')
        FROM ( SELECT [XML] = CONVERT(XML, '<i>' + REPLACE(@List, @Delimiter, '</i><i>') + '</i>').query('.')
      ) AS [A] 
      CROSS APPLY [XML].nodes('i') AS x(i) ) AS [Y]
      WHERE [Item] IS NOT NULL
);

...and use it wherever you want to split your list of values:

WHERE order IN (SELECT Item FROM [SplitIntegers]('1234, 12345, 123456', ','))

Note: the provided function only works for type INT.

score 2 · Accepted Answer · answered Jan 08 '20 at 12:49

2

That's not how parameters work - the in here will always be matching on a single value; try instead:

WHERE order IN (
    select cast(trim([value]) as int) from string_split(@passedOrders, ','))

(if the values aren't actually int, remove the cast step)

answered Jan 08 '20 at 12:49

Marc Gravell

1,026,079
266
2,566
2,900

Editing a string for it to be passed to a stored procedure

2 Answers2