Store the resulting list of a SELECT in a variable to count AND use the result

I have stored procedure that uses a cursor and for every fetch a duplicate SELECT, once to determine the number of IDs found (SELECT COUNT(ID) FROM TableA WHERE something.. @cur_var) and if the COUNT(ID) is bigger then a specified value, an UPDATE is issued for another Table WHERE the above IDs are used (SELECT ID FROM TableA WHERE something.. @cur_var)..

The idea is to have the SELECT stored in a variable (pseudo-code idlist = SELECT COUNT(ID) FROM TableA WHERE something.. @cur_var). Then the length of this list is used in the IF (pseudo-code IFlength(idlist)>100.
In this case the list is used again in the WHERE statement (pseudo-code again, UPDATE TableB SET value = @cur_var WHERE ID IN idlist)

I hope to be specific enough, I'd like to save the result of a SELECT in a variable. Then use the result in WHERE IN variable and the number of rows in the variable to use in an IF.

Working on Microsoft SQL Server Enterprise Edition v9.00.3042.00, i.e. SQL Server 2005 Service Pack 2

EDIT

I've tried to keep the procedure as reasonable as possible without giving away any private information..

My intention is to remove the duplicate code. The SELECT used in the IF and the SELECT used in the WHERE from the UPDATE are the same, except that I first need the COUNT(ID) and then the actual IDs

```
DECLARE @cur_id int;
SET @cur_id = 0;

DECLARE Cur CURSOR FOR
--this is actually a more complex SELECT but basically gives a list of IDs
SELECT ID FROM TableC ORDER BY ID DESC

OPEN Cur;
FETCH NEXT FROM Cur INTO @cur_id;

WHILE @@FETCH_STATUS = 0
BEGIN

--here I check if the count of found IDs (with value=NULL and IN the SELECT) is bigger than 5
IF
(
SELECT COUNT(ID) FROM TableA WHERE value=NULL AND ID IN
(
SELECT DISTINCT(ID) FROM TableB WHERE ID = @cur_id
)
) > 5
BEGIN
--if its bigger, set the value for every ID that is part of the ID list (the same select

If you want to put a query result in a table variable and iterate through the rows that can be easily done. Be careful with your loops of course.

DECLARE @Value int, @Label varchar(25)  
DECLARE @Tmp table (value int, label varchar(25)) 

INSERT INTO @Tmp (value, label) 
    SELECT 1, 'One' 
UNION SELECT 2, 'Two' 
UNION SELECT 3, 'Three' 

WHILE (SELECT count(1) FROM @Tmp) > 0 
BEGIN 
    SELECT TOP 1 @Value = value, @Label = label FROM @Tmp ORDER BY value 
    DELETE FROM @Tmp WHERE value = @Value 
    SELECT @Value 'value', @Label 'label' 
END

StackExchange Database Administrators Q#82474, answer score: 5