using IN and a list of values
-
Here is the problem,
I pass a parameter to my sp. That param is a varchar that contains a comma separated list of number that I want to have in my IN clause like this:
@list is : '1,2,3,4,5'
select * from test where var_id IN (@list)
var_id is an integer column ...
how can I get this to work ?
-
The only way to do this kind of thing is with dynamic SQL, e.g.
declare @sql varchar(255)
set @sql = 'select * from test where var_id in (' + @list + ')'
exec (@sql)It seems good practice however, to always mention that dynamic SQL might not necessarily be the way to go - pitfalls as well as benefits and all that, which are discussed here:
http://www.algonet.se/~sommar/dynamic_sql.html
Cheers,
mia
Life moves pretty fast. If you don't stop and look around once in a while, you could miss it.
Cheers,
mia
Life moves pretty fast. If you don't stop and look around once in a while, you could miss it. -
The thing is that I can't use exec (@sql) because I use the select statement in the FOR clause of a CURSOR declaration. I didn't find a way to put something else than a select in there.....
DECLARE MyCursor CURSOR FOR
SELECT ...
-
If necessary, you could select the results of the query into a temporary table and then use the cursor to iterate through the temporary table?
However, couldn't you do this without a cursor? As the values in @list are comma-separated, could you parse @list to get each value in turn and extract the records from the table individually, thus removing the need for the cursor. I would be inclined to do this if you're going to have to deal with the records individually anyway.
What I have in mind is something like the following:
declare @comma_pos int
declare @list varchar(255)
declare @value_as_int int
set @list = '1,2,3,4,5'
while len(@list) <> 0
begin
set @comma_pos = charindex(',', @list)
if @comma_pos <> 0
begin
-- Extract the value.
set @value_as_int = cast(substring(@list, 1, @comma_pos - 1) as int)
-- Remove the value from the string.
set @list = substring(@list, @comma_pos + 1, len(@list))
end
else
begin
-- The string must now only contain the final value.
set @value_as_int = cast (@list as int)
set @list = ''
end
-- Use the select to do whatever you need to do...
select * from test
where var_id = @value_as_int
endCheers,
mia
Life moves pretty fast. If you don't stop and look around once in a while, you could miss it.
Cheers,
mia
Life moves pretty fast. If you don't stop and look around once in a while, you could miss it. -
Wow! Thanks. I'm always impressed when people take all that time to help others.
Have a wonderful day mia !
-
how about:
Declare @varlist varchar(255)
Set @varList = ',1,2,3,5,' -- Commas before and after
SELECT *
FROM Table_Name
WHERE CHARINDEX(',' + Convert(varchar, TableId) + ',', @varList) > 0
* Noel -
And I'm always impressed at how often someone else has a much simpler solution
Learning new ways to think about things all the time on this site!Cheers,
mia
Life moves pretty fast. If you don't stop and look around once in a while, you could miss it.
Cheers,
mia
Life moves pretty fast. If you don't stop and look around once in a while, you could miss it. -
This site is simply the Best !!
2 times I posted and 2 times I got incredile solutions in less than an hour !!
Thanks to all of you.
-
Looks like Frank is away today but he normally refers people to these sites:
http://www.algonet.se/~sommar/arrays-in-sql.html
http://vyaskn.tripod.com/passing_arrays_to_stored_procedures.htm
Jeremy
- From time to time even I have to work.
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
The query of noeld is ok on small tables but I ran it (with 2 numbers in the list) on a 75 million rows table and it took 8 minutes to run even with an empty resultset.
With the 2 numbers hardcoded it takes not even a second ! I will try mia statement !
-
How about parsing the "List" into
something like ...
Declare @List Varchar(255)
set @list = '1,2,13,4,5'
Select Convert(Int, SubString(@List, StartPos, EndPos - StartPos + 1) ) as ListValues
From (
Select Min(Number) as StartPos,
NextComma -1 as EndPos
From (
Select Number,
Case When SubString(@List , Number, 1) <> ',' Then Number -1 Else 0 End as TheEnd,
CharIndex(',', @List + ',', Number) as NextComma
From Master.dbo.spt_Values
Where Type = 'P' and Number Between 1 and Len(@List)
) Parse
Group By NextComma
) ListValues
Then Either put results in #Temp table,
or just JOIN on the main query.
Once you understand the BITs, all the pieces come together
-
I didn't know you were using 75 MILLIONS records. I should have warn you about the cast because is not using any index.
I have nothing with that many records but I would be interested if you can tell me how this performs :
DECLARE @List varchar( 255 )
SET @List = ',1,2,3,7,5,'
SELECT * From
TABLE
Where TABLEId IN(
SELECT CAST (SUBSTRING( @List , n.n + 1 , CHARINDEX( ',' , @List , n.n + 1 ) - n.n - 1 ) AS int) AS Value
FROM Numbers AS n
WHERE n.n BETWEEN 1 And LEN( @List ) - 1
And
SUBSTRING( @List , n.n , 1 ) = ','
)
Assuming you already Have a "small" table called Numbers with numbers from 1 to the Max number of parameters (maybe 1-100?)
* Noel -
And Also Try the difference with a join:
SELECT * From
TABLE JOIN (SELECT CAST (SUBSTRING( @List , n.n + 1 , CHARINDEX( ',' , @List , n.n + 1 ) - n.n - 1 ) AS int) AS Value FROM Numbers AS nWHERE n.n BETWEEN 1 And LEN( @List ) - 1 And SUBSTRING( @List , n.n , 1 ) = ',') VAlues(c) ON TABLE.TABLEID = VALUES.c
Please, post your results
* Noel -
Hi,
when I'm passing data for use like this - for example when having key/value pairs with separators - e.g.,;
I use the following process;
CREATE FUNCTION Split_Varchar (@string varchar(8000),@sep varchar(1))
RETURNS
@ARRAY TABLE (ItemValue varchar(8000))
BEGIN
-- parse the attributetesting string and insert appropriate records
declare @value varchar(8000)
declare @start int
declare @finish int
declare @abort int
select @start = 1, @finish = 0, @abort=0
if ltrim(rtrim(@string)) = '' RETURN
if right(@string, 1) <> @sep select @string= @string+ @sep
WHILE @start<>0
BEGIN
select @abort = @abort+1
if @abort >8000 BREAK
select @finish = CHARINDEX(@sep, @string, @start)
if @finish= 0 BREAK
-- got the start and end points
SELECT @value = SUBSTRING(@string, @start, @finish - @start)
INSERT INTO @ARRAY (ITemValue) VALUES(convert(varchar(8000), @VALUE))
-- now move to the next value;
SELECT @start = @finish+1
IF @finish > len(@string) SELECT @start = 0
END
RETURN
END
this returns a table that you can reference, e.g.,
SELECT ItemValue
FROM dbo.Split_Varchar('123=54|124=54|776=76', '|')
that then returns a list of varchar ItemValues that you can then split down again - I've got 2 functions that do Get Key and Get Value returning these values as Integers.
you get the idea.
So taking the process forward (split_int returns ItemValue as an integer rather than a string);
SELECT T.*
from test T, dbo.Split_Int('1,2,3,4,5', ',') D
where
T.var_id = D.ItemValue
job done.
I often use this for bulk updating of data submitted from web-pages, e.g., a list of checkboxes on/off etc.
That way it can all be wrapped in a transaction as well.
hope this helps
cheers
Andy
Learning new ways to think about things all the time on this site!
From time to time even I have to work.Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply