AspBucket offers ASP.NET, C#, VB, Jquery, CSS, Ajax, SQL tutorials. It is the best place for programmers to learn

Monday, 4 January 2016

Cursor in Sql Server

In this article i will discuss how to use Cursor in Sql. A cursor is used to processed result set sequentially. In SQL procedures, a cursor makes it possible to define a set of data rows and perform complex logic on a row by row basis.
Syntaxes of cursor
  1. SET \ SELECT Statement - It initialize the variables.
  2. DECLARE Statement-It Declare variables .
  3. DECLARE CURSOR Statement - Declaring a cursor in scope.
  4. OPEN statement - Open the cursor to begin data row processing.
  5. FETCH NEXT Statement- Fetch next row from result set.
  6. WHILE Statement -Loops until rows remains for processing.
  7. BEGIN...END Statement - Start and end of the code block
  8. CLOSE statement - Releases the current data and associated locks, but permits the cursor to be re-opened.
  9. DEALLOCATE Statement - It Destroys the cursor
DECLARE @FirstName varchar(50), @LastName  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, @LastName  
   PRINT @FirstName + ' ' + @LastName  -- print the name
WHILE @@FETCH_STATUS = 0 
BEGIN 
   FETCH NEXT FROM cursorName 
   INTO @fName, @LastName  
   PRINT @FirstName + ' ' + @LastName  -- print the name

END 
CLOSE cursorName -- close the cursor
DEALLOCATE cursorName -- Deallocate the cursor

In this example printing First Name & Last Name using Cursor.

0 comments :

Post a comment

  • Popular Posts
  • Comments