Monday 25 February 2013

How to loop through records without using cursor in SQL?

How to loop through records without using cursor in SQL?

In SQL, if we use cursors, performance will be hit, to avoid the performance issue, we can use while loop with temporary tables.

First insert the records you want process into a temporary table and for example, here we can print the records one by one


Use Northwind
declare @ProdID int
declare @ProductName Varchar(200)

Set NOCOUNT on

Begin

Select top 10 * into #temp from products order by ProductID

While (Select COUNT(*) from #temp)>0

Begin

Select @ProdID=ProductID, @ProductName=ProductName  from #temp

Print @ProductName

Delete from #temp where ProductID=@ProdID

End

Drop table #temp
 End

Please click the sample below  to enlarge and view






No comments:

Post a Comment

You might also like:

Related Posts Plugin for WordPress, Blogger...