Aint no for...loops in T-SQL, so you can use something like:
DECLARE @cnt INT = 0;
WHILE @cnt < 10
BEGIN
PRINT 'Inside simulated FOR LOOP';
SET @cnt = @cnt + 1;
END;
PRINT 'Done simulated FOR LOOP';
--------------------------------------------------------------------------------
Practical example to create a reporting hierarchy for an org chart:
DECLARE @userid NVARCHAR(10) = 'A1234'
WHILE @userid is not null
BEGIN
select * from tblUsers where userId = @userid
set @userid = (select supervisorsUserId from tblUsers where userId = @userid )
END
-------------------------------------------------------------------------------
Another example, using PRINT and multiple variables:
DECLARE @UserId NVARCHAR(10) = 'A1234',
@FirstName NVARCHAR(max),
@LastName NVARCHAR(max);
WHILE @UserId is not null
BEGIN
select @FirstName=FirstName, @LastName=LastName
from tblUsers where UserId = @UserId
PRINT @FirstName + ' ' + @LastName + ' reports to '
set @UserId = (select SupervisorsUserId from tblUsers where UserId = @UserId )
END
=============================================
You can nest BEGIN...END's!!
=============================================
If you need to insert a "break" (like an "exit"), here's how:
BEGIN
-- do stuff
--- would cause an infinite loop if not caught!!
IF EXISTS (select userID from tblUsers where SupervisorsUserId = @UserId)
BEGIN
BREAK;
END
END
