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!

For our understanding and more clarity can you please provide the two different queries that you have to run... we see the one above, can you prove the other also, so that we will better understand what are changing and how — Surendra, Jul 28 '14 at 17:58
A "list" or "array" in SQL Server is always..... a **TABLE**! That's SQL Server's concept of "multiple values". So put your values into a table (temp table, table variables, table-valued parameter) and then use proper T-SQL joins to get what you need. **Don't reinvent the wheel** - it's already been invented too many times over.... — marc_s, Jul 28 '14 at 18:42

score 3 · Answer 1 · answered Jul 28 '14 at 17:52

If this is an ad hoc query that you're just going to run manually a handful of times, this is ugly but correct.

If this is something you'll be doing somewhat regularly, you can store your values in another table. You could have a table called "coderange" that had two columns, "minCode" and "maxCode". Then you'd rewrite this query:

SELECT DISTINCT a,b,c
    FROM mytable m
      JOIN coderange c
    WHERE  Code Between c.minCode and c.maxCode
     AND  Date > '20101231' 
     AND Date < '20120101'
     AND  ID IN ('3','6','7');

score 1 · Answer 2 · answered Jul 28 '14 at 17:55

1

I would probably create a parameterized stored procedure that would then be called over and over again with the correct values.

Some links:

Some other options would be to do this using dynamic SQL or create a basic .NET wrapper that creates the query you need and executes it.

answered Jul 28 '14 at 17:55

Abe Miessler

82,532
99
305
486

Thanks for the links. I just started working with SQL this past month and these will come in handy. – Jason Jul 29 '14 at 06:30

Surendra · Accepted Answer · 2014-07-28T20:43:14.737

0

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

edited Jul 28 '14 at 20:43

answered Jul 28 '14 at 18:25

Surendra

711
5
15

Is this a temporary table where I will need to declare it each time? I didn't say it, but that is my preference. If it is stored on the server, I just have to run it by the DBA guy. – Jason Jul 28 '14 at 18:30
if these values are permanent and don't change often, then by all means please change my variable table (@T) to a permanent table (T or some name). – Surendra Jul 28 '14 at 18:33
This is very new to me, so I'm not sure how to get this to function on my end. I get "Incorrect syntax near the keyword 'key'", which I assume means I need to replace it with something that can link the table I am querying (its actually a View) with @T. However, I'm not sure how this works. Why is the first value for each range a 1? – Jason Jul 28 '14 at 19:26
Edited to add the explanation let me know if you need more explaining on this – Surendra Jul 28 '14 at 20:43
Finally got it! Thank you! Turns out the word key is reserved in SMSS so I kept getting the error "Incorrect syntax near the keyword 'key'." I changed key INT to akey INT and T.key to T.akey and it works now. – Jason Jul 29 '14 at 06:20

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

3 Answers3