0

I am currently trying to split a string that I receive in a stored procedure, the current string format is: @string varchar(100) = (VALUE THAT COME AS A PARAMETER IN THE SP)'223,879,354,145,698,129'.

I need to split the string as I have to compare the numbers inside of it in a in statement such as below:

where number in (splited numbers) 
  and example = 'op' 
  and source not in ('qw', 'mn')

Do you know how I can split this string variable and save the result in a separate variable?.

I will also need the string value as it comes in the SP parameter for other purposes inside the SP

Barranka
  • 20,547
  • 13
  • 65
  • 83
user3701221
  • 1
  • possible duplicate of [How to parse a comma-delimited string variable in SQL](http://stackoverflow.com/questions/27556769/how-to-parse-a-comma-delimited-string-variable-in-sql) – M.Ali Dec 19 '14 at 18:38
  • By far the best option would be to change your procedure to use tabled valued parameters instead of these delimited strings. If you have no other option then look at this article on the topic. http://sqlperformance.com/2012/07/t-sql-queries/split-strings – Sean Lange Dec 19 '14 at 18:38
  • possible duplicate of [How to separate (split) string with comma in SQL Server stored procedure](http://stackoverflow.com/questions/23089579/how-to-separate-split-string-with-comma-in-sql-server-stored-procedure) – HaveNoDisplayName Dec 19 '14 at 18:40
  • that post is ok, however when i select the query i need to compare all the int values with a int column, any suggestions? – user3701221 Dec 19 '14 at 18:54
  • 1
    Which DBMS are you using? Languages for stored procedure are very specific to each DBMS ("SQL" is not a DBMS product, it's just a query language) –  Dec 19 '14 at 19:14
  • Instead of splitting the string what if you just used dynamic SQL? – SQLChao Dec 19 '14 at 19:29
  • @JChao that would open up a whole myriad of problems with sql injection. – Sean Lange Dec 19 '14 at 19:42
  • I still say the best thing you can do it to use a table valued parameter instead of wrestling with this the hard way. – Sean Lange Dec 19 '14 at 19:42
  • i am using sql server 2008 R2 – user3701221 Dec 19 '14 at 19:43

1 Answers1

0

Here's an example of what I mean in the comment to your question. This is for SQL Server.

declare @string varchar(100)
declare @dsql varchar(1000)

set @string = '223,879,354,145,698,129'

set @dsql = 'SELECT *
             FROM yourTable
             WHERE number in (' + @string + ')
               and example = ''op''
               and source not in (''qw'', ''mn'')'

exec (@dsql)
SQLChao
  • 7,709
  • 1
  • 17
  • 32