Using a string which comprises of values in a query

Question

So this sounds probably likea stupid question but I've never done something liek this....

I have a string that looks like this....

ValueID = 123, 234, 345, 466, 456

I also have a query that goes like this...

 Select * from Tbl1 where SomeValue In (123,234,345, 466, 456)

So what I'm trying to do now is this...

Select * from Tbl1 where someValue in (ValueID)

Is something like that doable?

Not the way you're implying - you would need to construct the query via dynamic sql. — Siyual, Feb 24 '17 at 21:12
[parameterize sql in clause](http://stackoverflow.com/questions/337704/parameterize-an-sql-in-clause?page=1&tab=oldest#tab-top) — SqlZim, Feb 24 '17 at 21:12
@SqlZim - i'm not trying to use parameters. I'm tryin to do it this way is it possible? — BobSki, Feb 24 '17 at 21:14
@BobSki you'd need to split the values up into individual values. — SqlZim, Feb 24 '17 at 21:16
You can use dynamic sql, or better... split it with a [string splitter](https://sqlperformance.com/2012/07/t-sql-queries/split-strings) and join on the table returned from your splitter. — S3S, Feb 24 '17 at 21:20
You can easily create parameters for each element in the IN clause for the SQL then just supply them as normal — Ňɏssa Pøngjǣrdenlarp, Feb 24 '17 at 21:39

score 3 · Accepted Answer · answered Feb 24 '17 at 21:16

3

I think you wanna use dynamic query:-

is the next code help ?

declare @ValueID varchar(200)
set @ValueID = '123, 234, 345, 466, 456'
exec ('Select * from Tbl1 where someValue in ('+ @ValueID +')')

answered Feb 24 '17 at 21:16

ahmed abdelqader

3,409
17
36

yeah in vb.net i did in (" & ValueID & ") and it does the job! – BobSki Feb 24 '17 at 21:20
@sqlzim - internal. i'm not worried – BobSki Feb 24 '17 at 21:27

score 1 · Answer 2 · answered Feb 24 '17 at 21:20

Your ValueID var should be a string.

ValueID = '123, 234, 345, 466, 456';

Now, it depends on the language that you're using but you'd need to concatenate this variable with the query string, given that SQL queries should be in String. To do this dynamically you might want to try and concatenate as @ahmed abdelqader said in the answer below.

declare @ValueID varchar(200)
set @ValueID = '123, 234, 345, 466, 456'
exec ('Select * from Tbl1 where someValue in ('+ @ValueID +')')

score 1 · Answer 3 · edited May 23 '17 at 11:53

It is doable.

Follow the approach given here.

DECLARE @MyList TABLE (Value VARCHAR(10))
INSERT INTO @MyList VALUES ('123')
INSERT INTO @MyList VALUES ('234')
[...]

SELECT *
FROM MyTable
WHERE MyColumn IN (SELECT Value FROM @MyList)

which is my preferred answer from Define variable to use with IN operator (T-SQL). This accomplishes the goals of the original question, I think. By building up a list of values, in this case string values, the IN operator will work as expected. If you want INT values, just change VARCHAR to INT and remove the quotes.

score 0 · Answer 4 · answered Feb 24 '17 at 21:25

Using a CSV Splitter function by Jeff Moden:

create table v (ValueId varchar(8000))
insert into v values ('123, 234, 345, 466, 456');

create table t (someValue int);
insert into t values (345),(346)

select *
from t
where someValue in (
  select x.Item 
    from v
    cross apply (
        select Item 
        from [dbo].[delimitedsplit8K](v.ValueId,',')
        ) as x
    where x.Item <>''
    )

test setup: http://rextester.com/GRNWY13179

returns:

+-----------+
| someValue |
+-----------+
|       345 |
+-----------+

splitting strings reference:

Using a string which comprises of values in a query

4 Answers4

Linked