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

You may be looking for a fetch while scenario which is the closest thing to a for each or do while in VB or C# code that I know of. I have a simple example below. There are some better FETCH examples in MS Docs fetch-transact-sql

Answer 3

SQL is a query language => Gets results sets from a statement.
What you are trying to achieve is something like a (business) logic... that should be handled by an application and not in a database engine.

So you code, e.g., a PHP application.

<PHP>
Do something
...
connect to database
fire SQL statement
get you resultset
...
do something with that result set
</PHP>

So you have to lookup the corresponding php-code for a loop.

yes, technically it is possible to do something like this in SQL Server but try to avoid it.
If you have to do such stuff within the db-engine then cursors are good idea.

Answer 4

In SQL Server you can use a CURSOR to loop through the set of rows. My favorite blog on this topic still remains this one
bradsruminations.blogspot.com/2010/05/truth-about-cursors-part-1.html

When I need to use the CURSOR in the code, I go to this blog post to code as that blog suggests. It is also a fun reading, I enjoy every time.

Answer 5

SQL is a "set based" language. You rarely want to loop in SQL. Looping is a last resort.

select age from usersTable
WHERE age = 30