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?
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 +')')
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 +')')
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.
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:
