DotNet Mirror
  DNM facebook   DNM Google+   DNM Twitter   

Looping through rows without cursors in Sql server using while loop

By Ashok Nalam on 22 Dec 2012 | Category: SQL Server | Tagged: cursor tsql 
In this snippet we will see how to iterating through the table rows data with out using cursor in SQL server.
  Discuss Add Comment   |  
Add rating Rate this resource   

Introduction

In the below code, we will see how to iterating through the table rows data with out using cursor in SQL server.

declare @tempEmpTable table(EmpID int , EmpName nvarchar(100))
declare @varEmpID int, @varEmpName nvarchar(100)
insert into @tempEmpTable
select 1, '1st Employee' union all
select 2, '2nd Employee' union all
select 3, '3rd Employee' union all
select 4, '4th Employee' union all
select
5, '5th Employee' union all
select 6, '6th Employee' union all
select 7, '7th Employee' union all
select 8, '8th Employee' union all
select 9, '9th Employee'

select top 1 @varEmpID = EmpID, @varEmpName = EmpName from @tempEmpTable
while @varEmpID is not null
begin
print @varEmpName
delete @tempEmpTable where @varEmpID = EmpID
set @varEmpID = null
select top 1 @varEmpID = EmpID, @varEmpName = EmpName from @tempEmpTable

end

Output:

1st Employee
2nd Employee
3rd Employee
4th Employee
5th Employee
6th Employee
7th Employee
8th Employee
9th Employee

Note
: make sure that while deleting the entry row from table it should delete one entry at a time.
  Discuss Add Comment    
Add rating Rate this resource   
About the Contributor
Member Since : 10 Dec 2012
Member Points (Level) : 8666  (Professional)
Location : INDIA
Home Page : http://dotnetmirror.com
About : I am admin of this site.
Rate this resource
 
Add your Comment
Name Email WebSite
Captcha Refresh


Comments (0)
No comments found, click here to add comment.