Tuesday, October 1, 2019

SQL Server Nested WHILE Loop Example

To use while loop in stored procedure we need to write the query like this


DECLARE @loop INT
SET @loop = 0
WHILE @loop <= 10
BEGIN   
SET @loop = @loop + 1   
PRINT @loop
END
If we run above query we will get output like as shown below

Output

If we want to Nested while loops we need to write the query like this


DECLARE @loop1 INT
DECLARE @loop2 INT
SET @loop1 = 1
WHILE @loop1 <= 3
BEGIN   
SET @loop2 = 1   
WHILE @loop2 <= 2   
BEGIN       
PRINT CONVERT(VARCHAR, @loop1) + ' * ' + CONVERT(VARCHAR, @loop2)+ ' = ' + CONVERT(VARCHAR, @loop1 * @loop2)       
SET @loop2 = @loop2 + 1   
END   
SET @loop1 = @loop1 + 1
END
If we run above query we will get output like as shown below

Output

No comments:

Post a Comment

Get max value for identity column without a table scan

  You can use   IDENT_CURRENT   to look up the last identity value to be inserted, e.g. IDENT_CURRENT( 'MyTable' ) However, be caut...