CODEWITHSUNDEEP
sqlsql-servervb.net
BobSki
BobSki

Reputation: 1552

Using a string which comprises of values in a query

Using a string which comprises of values in a query

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?

Upvotes: 0

Views: 677

Answers (4)

Mark Ginsburg
Mark Ginsburg

Reputation: 2269

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.

Upvotes: 1

SqlZim
SqlZim

Reputation: 38023

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:

Upvotes: 0

James
James

Reputation: 698

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 +')')

Upvotes: 1

ahmed abdelqader
ahmed abdelqader

Reputation: 3560

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 +')')

Upvotes: 3

Related Questions