/*------------------------------------------------------------------------------------------------------------

--looping_in_sql_server_stored_procedurer_with_while_loop_0029.sql


One way you can perform looping operation in SQL server is to use a while loop.  In this example we are going 
to use the StateProvince table as we demonstrated earlier with a cursor.  We will also use the loop counter and 
also the number of records from the table to iterate through the result set. In this sample we need to print the 
state and the territory information one record at a time, maybe for a report. 

NOTE:

Generally using a while loop is preferred over using a cursor.


------------------------------------------------------------------------------------------------------------*/


USE AW
GO


-- declaration of variables

DECLARE @loop_count int
DECLARE @rec_count int
DECLARE @StateProvinceID int
DECLARE @Name varchar(50)
DECLARE @TerritoryID varchar(50)

--set the value for loop counter and also the record count in table

SELECT @rec_count=COUNT(*) FROM [AW].[Person].[StateProvince]
SET @loop_count=1



--While loop with loop counter check against record count

WHILE @loop_count<@rec_count

	BEGIN

		SELECT @StateProvinceID=[StateProvinceID]
				,@Name=[Name]
				,@TerritoryID=[TerritoryID]
		  FROM [AW].[Person].[StateProvince]
			WHERE [StateProvinceID]=@loop_count
		  ORDER BY [StateProvinceID]
		  
			--print the info one record at a time
			
			PRINT 'For this State ' + @Name + ', the Territory ID is ' + @TerritoryID
		
		--increment the loop counter
		
		SET @loop_count=@loop_count+1

	END --loop back



--Output is below


/*   

For this State Alberta, the Territory ID is 6
For this State Alaska, the Territory ID is 1
For this State Alabama, the Territory ID is 5
For this State Arkansas, the Territory ID is 3
For this State American Samoa, the Territory ID is 1
For this State Arizona, the Territory ID is 4
For this State British Columbia, the Territory ID is 6
For this State Bayern, the Territory ID is 8
For this State California, the Territory ID is 4

...

   

Compiled by http://www.learningsqlserver2008.com/, Kash Data Consulting LLC 

Disclaimer:

All sample code and scripts are compiled by Kash Data Consulting LLC for illustrative purposes only. Kash Data Consulting LLC, 
therefore, cannot guarantee or imply reliability, or function of this code or scripts.

All code contained herein are provided to you "AS IS" without any warranties of any kind. Please test all code and 
scripts in test environment before deployment in production systems.

*/