how to loop in sql server

Question

Hi members
i am a beginner in sql i want understand the select and for and while loop in sql please help

if i make a select query the result is a column with many rows :

how i can use that rows in a variable or table and use it after

for example :
declare @age int
'select age from usersTable '

it will return a column of age now i want test row by row
like in vb.net :
for i=0 to age.rows.count-1???
if @age=30
begin
print @age
break
end
i have no idea how the select work into a variable or table please help

Answer 1

Hi @HOUSSEM MAHJOUBI
Try this:

DECLARE @SampleTable TABLE (age INT,RNum INT) ;  
  
INSERT INTO @SampleTable  
SELECT age,ROW_NUMBER()OVER(ORDER BY age) AS RNum  
FROM usersTable   
  
DECLARE @Counter INT , @MaxRNum INT,  @age INT  
SELECT @Counter = min(RNum) , @MaxRNum = max(RNum)   
FROM @SampleTable  
   
WHILE(@Counter IS NOT NULL AND @Counter <= @MaxRNum)  
BEGIN  
   SELECT @age = age  
   FROM @SampleTable WHERE RNum = @Counter  
   IF @age = 30  
     BEGIN    
     PRINT @age  
	 BREAK    
	 END  
   SET @Counter  = @Counter  + 1          
END  

Refer to this article for more examples: SQL WHILE loop with simple examples

Best regards,
LiHong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

There are situations where you write loops in SQL Server, but they are not common. If you are true beginner, save learning writing loops for later.

To assign a variable from a query you do:

SELECT @age = age 
FROM  UserTable
WHERE UserID = @userId

Queries where you assign variables, typically return exactly one row. If they return more than one row, @age will be set to the "last" row - but exactly what that means is not clear. And if the query returns zero rows, beware that @age will retain its value - it will not be set to NULL.