1

I have a select statement that involves a "IN" clause:

SELECT *
FROM [database]
WHERE [variablename] IN (1..2..3..4..)

Some of my variables are a letter followed by a number in sequential order. For example, V1, V2, V3....V30.

I would like to avoid typing every variable within the statement, is there a way to create an array or list that could store those 30 variables for me and then call them in the query? Or generate the variables by range?

alybaba726
  • 390
  • 4
  • 14
  • 2
    http://stackoverflow.com/questions/337704/parameterize-a-sql-in-clause?rq=1 – Steve Jun 24 '14 at 19:54
  • This helps partially, the second part though--is there a way to create a list of variables (from V1 to V30), for example, without typing thirty completely out? – alybaba726 Jun 24 '14 at 19:58

1 Answers1

1

Put the values in a table (could be a temp table) and join on that table.

declare @i int = 0

while  @i < 30
begin 
    insert into tableName (columnName)
    select 'V' + cast(@i as varchar)
    select @i = @i + 1
end

select  *
from    [database] a inner join [database] b on a.columnName = b.columnName
macoms01
  • 1,110
  • 13
  • 22
  • Which would be the same as me manually typing them in the query, I'm trying to avoid that. I have ten different categories (A-J) with a numeric range of 1-30, that's 300 entries into a temp table! – alybaba726 Jun 24 '14 at 20:00
  • See my edit - are the values just 1-30 or do they change depending on some situation? – macoms01 Jun 24 '14 at 20:02
  • Yes, this is perfection. Thank you so much! – alybaba726 Jun 24 '14 at 20:05