Can criteria be re-used in a T-SQL query?

Question

I have a SQL query that looks something like this:

SELECT
    o.name,
    o.type_id, 
    (SELECT COUNT(*) FROM a WHERE type_id = o.type_id AND id IN ('1, 2, 3 ... 1000')) AS count_a, 
    (SELECT COUNT(*) FROM b WHERE type_id = o.type_id AND id IN ('1, 2, 3 ... 1000')) AS count_b, 
    (SELECT COUNT(*) FROM c WHERE type_id = o.type_id AND id IN ('1, 2, 3 ... 1000')) AS count_c
FROM o

In the subqueries (count_a, count_b and count_c) the criteria specified in the IN clause is the same for each, but its a REALLY long list of numbers (that aren't in fact sequential) and im concerned that:

a) Im slowing the query down by making it too long
b) Its going to get too long and cause an error eventually

Is there a way to alias/reference that list of criteria (perhaps as a variable?) so that it can be re-used in each of the three places it appears in the query? Or am I worrying for nothing?

UPDATE

Given the suggestion of using a CTE, I have changed the query above to work like this for now:

WITH id_list AS (SELECT id FROM source WHERE id IN ('1, 2, 3 ... 1000'))
SELECT
    o.name,
    o.type_id, 
    (SELECT COUNT(*) FROM a WHERE type_id = o.type_id AND id IN (SELECT id FROM id_list)) AS count_a, 
    (SELECT COUNT(*) FROM b WHERE type_id = o.type_id AND id IN (SELECT id FROM id_list)) AS count_b, 
    (SELECT COUNT(*) FROM c WHERE type_id = o.type_id AND id IN (SELECT id FROM id_list)) AS count_c
FROM o

This cuts the overall length of the query down to about a third of what it was, and although the DB appears to take a couple of milliseconds longer to execute the query, at least I wont run into an error based on the length of query being too long.

QUESTION: Is there a quick way to break a comma separated list of numbers (1, 2, 3 ... 1000) into a result set that could be used as the CTE?

Yes. You can use the with keyword. This is also known as Common Table Expressions. — Dan Bracuk, Jan 30 '14 at 14:27
String parsing is an old, old topic. Search SO for "sql string parse" and you'll find dozens of answers. — Philip Kelley, Jan 30 '14 at 15:08
The answer on the second question that you've added recently depends on the version of sql-server. — Tim Schmelter, Jan 30 '14 at 15:35

score 3 · Answer 1 · answered Jan 30 '14 at 14:27

3

You could use a common-table-expression(CTE):

WITH Numbers AS
(
    SELECT N 
    FROM (SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 1000)
    AS T(N)
)
SELECT
    o.name,
    o.type_id, 
    (SELECT COUNT(*) FROM a WHERE type_id = o.type_id AND id IN (SELECT N FROM Numbers)) AS count_a, 
    (SELECT COUNT(*) FROM b WHERE type_id = o.type_id AND id IN (SELECT N FROM Numbers)) AS count_b, 
    (SELECT COUNT(*) FROM c WHERE type_id = o.type_id AND id IN (SELECT N FROM Numbers)) AS count_c
FROM o

answered Jan 30 '14 at 14:27

Tim Schmelter

450,073
74
686
939

Thanks Tim, I updated my question with your suggestion and added one more query if you don't mind :) – Jimbo Jan 30 '14 at 15:07
@Jimbo: your last question depends on the version of sql-server, you could use this approach in all versions: http://stackoverflow.com/a/5739576/284240 However, here is a complete overview over all approaches for all versions which is worth reading anyway: http://www.sommarskog.se/arrays-in-sql.html – Tim Schmelter Jan 30 '14 at 15:40

score 0 · Answer 2 · answered Jan 30 '14 at 14:45

If your ids are foreign keys into some other table, then you could make a table variable from your list, and then join into that repeatedly.

DECLARE @id_list TABLE (
  id INT
)

INSERT INTO @id_list
SELECT id
FROM pk_location
WHERE id IN ('1, 2, 3 ... 1000')

SELECT
    o.name,
    o.type_id, 
    (SELECT COUNT(*) FROM a INNER JOIN @id_list i ON a.id = i.id WHERE type_id = o.type_id) AS count_a, 
    (SELECT COUNT(*) FROM b INNER JOIN @id_list i ON b.id = i.id WHERE type_id = o.type_id) AS count_b, 
    (SELECT COUNT(*) FROM c INNER JOIN @id_list i ON c.id = i.id WHERE type_id = o.type_id) AS count_c
FROM o

Can criteria be re-used in a T-SQL query?

2 Answers2