How to store a list of values as a variable to perform multiple SQL queries?

Question

Is there a way to store the WHERE clause statements as a callable variable?

I need to run the query below about 20 times using different 'Date' and 'ID' values, but the 'Code' values will stay the same. However, after the 20 queries, I will need to change the 'Code' values to another set of values and using the same 20 'Date' and 'ID' combinations.

I am using SQL Server Management Studio 2012.

Edit: This is actually a subquery for me to count the number of records that results from it. Each count query is union'ed to additional count queries so I can execute all queries at once and have the result show 1 column with the counts. I would like to know how to just make an excel table with the results where each column is specific to a 'Code' set, but I haven't looked into it yet.

SELECT DISTINCT a,b,c
    FROM mytable
    WHERE     (Code BETWEEN '201' AND '205') OR (Code BETWEEN '211' AND '215') OR (Code BETWEEN '241' AND '245') OR (Code = '450')
          OR  (Code BETWEEN '381' AND '387') OR (Code BETWEEN '391' AND '397') OR (Code BETWEEN '401' AND '420') OR (Code BETWEEN '441' AND '444') 
          OR  (Code BETWEEN '358' AND '360') OR (Code BETWEEN '371' AND '937') OR (Code = '499')                 OR (Code BETWEEN '218' AND '239'))
         AND  (Date > '20101231' AND Date < '20120101')
         AND  (ID IN ('3','6','7'))

How my Code values change:

WHERE   (Code IN ('791','792'))
     OR (Code BETWEEN '801' AND '899')
     OR (Code BETWEEN '101' AND '125')
     OR (Code BETWEEN '401' AND '429')


WHERE   (Code BETWEEN '281' AND '749') OR Code = '2037'

There are 2 other Code lists, for a total of 5, which involve BETWEEN, IN, and = statements. I can post these as well if it helps, but they are follow the same manner of declaration as above.

The Date values are either for calendar years or quarters. The ID values are either stated via IN, NOT IN, or =. They are all char, varchar, tinyint, or date.

Alternative: I could just copy and paste over and over replacing the code but would like to improve my coding ability. Also, Find and Replace only reads single lines. I can't get it to work for multiple lines, such as the WHERE clause. I've heard of regular expressions but am not sure how to use them to do what I want.

Thanks for the help!

Answer 1

In this scenario, I will use a variable table to store the code values like below

The @t table here is a variable table, I.e an in memory table, but it can also be a permenant table

The table contains a value called key, the key column will contain an integer for each set of code ranges you want to store.

For the below example I put down a set of value 1 and 2.

declare @t TABLE 
(
key INT, FROM_code VARCHAR(10), TO_CODE VARCHAR(10)
)


insert into @T VALUES (1,'791','792')
insert into @T VALUES (1,'801','899')
insert into @T VALUES (1,'101','125')
insert into @T VALUES (1,'401','429')

Insert into @t values (2,'281','749')
Insert into @t values (2,'2037','2037')

Now, in the where select statement can be written as below

Declare @KEY INT
-- PASS key as input parameter to the stored procedure.
Set @key =1


SELECT distinct a,b,c
From <my_table> M
JOIN @T T
ON T.key = @key
WHERE m.code between from_code and to_code

For the second iteration just replace @key=1 with @key=2