Using of While Loop in a Stored Procedure.

	
	

	
		Description :  Using of While Loop in a Stored Procedure, which I have written in MicroSoft SQL Server 2000. This is used 

		 for certain action on a table. Scenario: I have got an requirement that, Currently in a table I have 1000 Assets(computers), when administrator wants to assign assets to delivery center, then administrator enters only numeric text like 100 assests need to assign to to Delivery Center, Than using while loop we are randomly picking the assets and assign to the Delivery Center.
	
	

	

	
		CREATE Procedure Ps__UpdateAssetBul
		------Variable that needs to be passed.-------
		------Global Variables-------
		@CNTINP Int, --Count that needs to assigned from Total Count by dynamically.
		@DCenterId Varchar(50), --Delivery Center to which the assets needs to be assigned.
		@Result Varchar(50) Output --Status that returns to the user.
		As      
		Begin      
				--Start of While Loop for assigning number of products that are to be assigned for an delivery center.
  				WHILE(@CNTINP != 0)      
  				BEGIN   
					------Local Variables-------
					Declare @AssetStatus Varchar(50) ------To check the status of the product, whether it is already assigned.-------
					Declare @Assetid varchar(50)  ------To store the assetid of the product, whether it is already assigned.-------    
					--We will dynamically pick the Asset from the number of assets available.
					select Top 1 @Assetid =  assetid 
					from _Asset_Details e1 where Prodid = @ProdId   And Status = 'unassigned'  
					--We will pick the AssetStatus of the Selected AssetId.
					Select @AssetStatus = Status From AMS_Asset_Details      
					Where DeletionSTatus = 'U' And AssetId = @Assetid
					--Condition to verify whether it is not assigned to any center.
					If(@AssetStatus = 'UnAssigned')      
					Begin   
						--If the condition satisfies then it updates.
						Update AMS_Asset_Details      
						Set Status = 'Assigned'      
						Where AssetId = @Assetid And DeletionStatus = 'U'
					End   
					Else  
					Begin  
						--If the Status of the Asset is Assigned. Then we check the Count of the Assets is available.
						If((select count(*)
							from AMS_Asset_Details 
							where Prodid = @ProdId And Status = 'unassigned') > 0)
						Begin
							--If there are no Assets available then we increment the count.
							Set @CNTINP = @CNTINP+1  
						End
					End   
					SET @CNTINP = @CNTINP - 1      
				END      
			--if(@@error <> 0), condition to check that if there are any errors occured during the Updation.
			If(@@Error <> 0)      
			Begin      
				--If an error encounters during the updation.
				Set @Result = 'Error'      
			End      
			Else      
			Begin      
				--If there is no error, then output returns as 'Success'.
				Set @Result = 'Success'      
			End      
		End