CODEWITHSUNDEEP
arrayssql-servervariables
maliks
maliks

Reputation: 1112

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

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!

Upvotes: 1

Views: 776

Answers (2)

James Z
James Z

Reputation: 12317

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)

Upvotes: 1

Felix Pamittan
Felix Pamittan

Reputation: 31879

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)

Upvotes: 1

Related Questions