List [a,b,c] to a,b,c

Question

I receive a proc with this on parameter:

exec do_something [a,b,c]

I need to make a query like:

 select * from B where b not in ("a","b","c")

How can I make this transformation?

Answer 1

A quick/easy solution would be to use the str_replace() function to reformat the input parameter, then build a dynamic query using said modified parameter, eg:

declare @param varchar(100),
        @query varchar(1000)

select  @param = 'a,b,c'

select  @param = '"' + str_replace(@param,',','","') + '"'

print   "@param: %1!",@param

select  @query = 'select * from B where b not in (' + @param + ')'

print   "@query: %1!",@query

exec(@query)
go

@param: "a","b","c"
@query: select * from B where b not in ("a","b","c")

.... results of running query .....