MS Sql Server - Alternative to Cursor

Hello Friends,

If you are working with MS SQL Server, you may familier with Cursor which we use to loop through a recordset. But as everyone knows, cursors are expensive. They consume more memory and also take more time.

So lets discuss how to avoid cursors but how to loop as we use a cursor.

Create a temp table.

 create table #ttProduct (  
      [id] int identity(1,1) NOT NULL,  
      [idProduct] inIdentifier not null,       
      [idCategory] inIdentifier not null, ...etc  

and here we get selected records into the temp table.

 insert #ttProduct(  
 [idProduct],   
 [idCategory],   
 [lbCategory],  
 .  
 .  
 )  
 select   
      cp.idProduct,  
      po.idCategory,  
      cat.ShortName AS 'lbCategory',  
 .  
 .  

And now let's see how it is done.

 while exists (select * from #ttProduct)  
           begin  
                select top 1 @recordId = id, @idProduct=idProduct  
                from #ttProduct  
                order by id asc  
                --select * from #ttProduct where id = @recordId  
                -- Do something with your TableID  
                delete #ttProduct  
                where id = @recordId  
           end  
 drop table #ttProduct  

it is basically a while loop and each iteration we delete the top record once processed. So it will ultimately run out from records. End of everything, drop the tempory table too.

Hope you have got your idea.