Click here to Skip to main content
14,695,504 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I have this
declare @Identity int
set @Identity = 100

declare @table1 as table (Id int)
insert into @table1 exec  [usp_GetIds] @Identity,2

declare @table2 as table (Id int)
insert into @table exec  [usp_GetIds] @Identity,1


Now i want to do logic based on each record filled in @table2 so , i need to loop

What I have tried:

So after inserting i want to do the following
let's say table2 retrieved this
Id  Name
--  ----
1   name1
2   name2
3   name3


I want to loop through each record retrieved and do this
while @table2 is not null  -- it seems i can't say while @table2
(
select count(Id)from @table1 where @table1.Id=@table2.Id 
--it seems i can't say @table1.field
exec usp_doSth(@table2.Id)
)
Posted
Updated 12-Aug-20 4:44am
v4
Comments
CHill60 12-Aug-20 4:29am
   
Do not loop!
SQL is SET based
Have a look at my article Processing Loops in SQL Server[^] - at the end it will tell you how to do loops in SQL Server - the rest of the article gives examples of why you usually don't need a loop and how to do that
Member 14800672 12-Aug-20 4:40am
   
how can i use the while with the table variable i created?
Jörgen Andersson 12-Aug-20 5:18am
   
You cannot, because it's a table variable.
It refers to a table, not a value, or even a row with values.
Now I suggest you read the article you were linked. It's good

1 solution

As mentioned in the comments, you should always try to avoid loops in SQL code. But in this case, something like this should work:
WHILE Exists(SELECT 1 FROM @table2)
BEGIN
    DECLARE @Id int;
    SELECT TOP 1 @Id = Id FROM @table2 ORDER BY Id;
    EXEC ups_doSth @Id;
    DELETE FROM @table2 WHERE Id = @Id;
END;
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900