Friday, October 24, 2014

using cursor in sql server

Posted by Gnani Palepu  |  No comments

 how to use cursor in sql server using stored procedure  
 see below  
 syntax  
 ---------:  
 this is syntax for how to use cursor in sql server using stored procedure  
 DECLARE @fName varchar(50), @lName varchar(50)  
 DECLARE cursorName CURSOR -- Declare cursor  
 LOCAL SCROLL STATIC  
 FOR  
 Select firstName, lastName FROM myTable  
 OPEN cursorName -- open the cursor  
 FETCH NEXT FROM cursorName  
   INTO @fName, @lName  
   PRINT @fName + ' ' + @lName -- print the name  
 WHILE @@FETCH_STATUS = 0  
 BEGIN  
   FETCH NEXT FROM cursorName  
   INTO @fName, @lName  
   PRINT @fName + ' ' + @lName -- print the name  
 END  
 CLOSE cursorName -- close the cursor  
 DEALLOCATE cursorName -- Deallocate the cursor  
 sample example:  
 ----------------  
 this is sample example for how to use cursor in sql server using stored procedure  
 DECLARE @ColExpir datetime  
 DECLARE @ColFallprotec datetime  
 DECLARE @ColWorkid int  
 --------------------------------------------------------  
 DECLARE @MyCursor CURSOR  
 SET @MyCursor = CURSOR FAST_FORWARD  
 FOR  
 SELECT Table_Training_Detalis.DateExpires,Table_Training_Detalis.Worker_ID  
 FROM  Table_Courses   
 OPEN @MyCursor  
 FETCH NEXT FROM @MyCursor  
 INTO @ColExpir,@ColWorkid  
 WHILE @@FETCH_STATUS = 0  
 BEGIN  
 update Table_Workers set WHIMIS= @ColExpir where Worker_ID=@ColWorkid  
 FETCH NEXT FROM @MyCursor  
 INTO @ColExpir,@ColWorkid  
 END  
 CLOSE @MyCursor  
 DEALLOCATE @MyCursor  

11:51 PM Share:

0 comments:

Get updates in your email box
Complete the form below, and we'll send you the best coupons.

Deliver via FeedBurner
Proudly Powered by Blogger.
back to top