How to use cursor in stored procedure in MS SQL


This example will tell you How to use cursor in stored procedure in MS SQL. Cursor is complex terms in sql but when you start to use it its become simple. You can understand cursor well by using following example.

 DECLARE @ testcols1 AS varchar(30)  
 DECLARE @ testcols2 AS varchar(30)  
 DECLARE c_Cursor CURSOR FOR SELECT Col1, Col2 from Customer  
 OPEN c_ Cursor  
 FETCH NEXT FROM c_ Cursor INTO @ testcols1, @ testcols2  
 WHILE @@FETCH_STATUS=0   
 BEGIN   
    Exec ('select ' + @ testcols1 + ' , ' + @vcol2 + ' from CustE)  
    FETCH NEXT FROM c_ Cursor INTO @ testcols1, @ testcols2  
 END   
 CLOSE c_ Cursor  
 DEALLOCATE c_ Cursor