How to declare a variable containing more than one integer values SQL Server

Question

I want to store some id's in a variable then to use this variable multiple times wherever I have to use in queries. I used this format as:

DECLARE @aid INT
SELECT @aid = AP.aid
FROM 
    sub_aminer_paper AP
GROUP BY 
    AP.aid
HAVING 
    MIN(p_year) = 1990 AND MAX(AP.p_year) = 2014 AND COUNT(AP.pid) BETWEEN 10 AND 40

SELECT 
    * 
FROM 
    sub_aminer_paper 
WHERE 
    aid = @aid

But this gives me the result only for any single ID from a list of ID's as I used query to retrieve multiple ID's from table sub_aminer_paper.

Please help and Thanks!

Create a temp table and store the results in it and then do the select. INT can not store more than one value. — Abhishek
– Abhishek, Commented Sep 21, 2015 at 10:47
Or a table variable or use this variable inside a while loop. — ProblemSolver
– ProblemSolver, Commented Sep 21, 2015 at 11:14

Felix Pamittan · Accepted Answer · 2015-09-21 10:47:36Z

1

What you want is a table variable:

DECLARE @aid TABLE(id INT PRIMARY KEY)

INSERT INTO @aid
SELECT AP.aid
FROM sub_aminer_paper AP
GROUP BY AP.aid
HAVING 
    MIN(p_year) = 1990
    AND MAX(AP.p_year) = 2014
    AND COUNT(AP.pid) BETWEEN 10 AND 40

SELECT * 
FROM sub_aminer_paper 
WHERE 
    aid = (SELECT id FROM @aid)

answered Sep 21, 2015 at 10:47

Felix Pamittan

31.9k7 gold badges43 silver badges71 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

James Z · Accepted Answer · 2015-09-21 10:49:57Z

1

You could also do this without using a variable, with something like this:

SELECT * 
FROM sub_aminer_paper AP1
where exists (
  SELECT 1
  FROM sub_aminer_paper AP2
  WHERE AP1.aid = AP2.aid
  GROUP BY AP2.aid
  HAVING 
    MIN(AP2.p_year) = 1990
    AND MAX(AP2.p_year) = 2014
    AND COUNT(AP2.pid) BETWEEN 10 AND 40)

answered Sep 21, 2015 at 10:49

James Z

12.3k10 gold badges27 silver badges48 bronze badges

1 Comment

James Z Over a year ago

Or do the same thing using a CTE or derived table

Collectives™ on Stack Overflow

How to declare a variable containing more than one integer values SQL Server

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related