SQL SERVER – Simple Example of Cursor.

Hi Friends,in this article I will explain about SQL SERVER – Simple Example of Cursor..
I already explained in the previous articles about How to Scroll Page Automatically by few pixels after every few seconds using JQuery,VS Shortcuts | Shortcut keys for Visual Studio and JQuery:How to Disable Cut,Copy and Paste of TextBox in ASP.NET

DBScript for User_Details table

CREATE TABLE User_Details (   User_id INT IDENTITY (1, 1) PRIMARY KEY,   UserName VARCHAR(100),   Gender VARCHAR(10),   Address VARCHAR(200),   Password VARCHAR(10) ) INSERT INTO User_Details (UserName, Gender, Address)   VALUES ('Kishore', 'Male', 'XXXXXXXX', '123456') INSERT INTO User_Details (UserName, Gender, Address)   VALUES ('Satyam', 'Male', 'XXXXXXXX', '123456') INSERT INTO User_Details (UserName, Gender, Address)   VALUES ('Rithvika', 'Female', 'XXXXXXXX', '123456') INSERT INTO User_Details (UserName, Gender, Address)   VALUES ('SaiR', 'Female', 'XXXXXXXX', '123456') INSERT INTO User_Details (UserName, Gender, Address)   VALUES ('SambhaShiva', 'Male', 'XXXXXXXX', '123456')

DECLARE @USERID INT --we need to declare at least one variable DECLARE GetUserID_CURSOR CURSOR FOR SELECT user_ID FROM User_Details OPEN GetUserID_CURSOR FETCH NEXT FROM GetUserID_CURSOR  --Start the cursor INTO @USERID WHILE @@FETCH_STATUS = 0  --while there is a loaded record, keep processing BEGIN print ('write whatever you want to do like update/insert/delete/stored proc/etc.') print (@USERID) FETCH NEXT FROM GetUserID_CURSOR INTO @USERID  --fetch next record END CLOSE GetUserID_CURSOR   --Close the cursor DEALLOCATE GetUserID_CURSOR --Deallocate the cursor

SQL Server Cursor Components

Based on the example above, cursors include these components:

• DECLARE statements - Declare atleast one
• SET\SELECT statements - Initialize the variables to a specific value
• DECLARE CURSOR statement - Populate the cursor with values that will be evaluated
• OPEN statement - Open the cursor to begin data processing
• FETCH NEXT statements - Assign the specific values from the cursor to the variables
• WHILE statement - Condition to begin and continue data processing
• BEGIN...END statement - Start and end of the code block
• Data processing - In this example, this logic is to backup a database to a specific path and file name, but this could be just about any DML or administrative logic
• CLOSE statement - Releases the current data and associated locks, but permits the cursor to be re-opened
• DEALLOCATE statement - Destroys the cursor

The output of the above code as shown in the below figure.

If you like my blog or articles, you can appreciate by leaving your comments or Liking my Facebook pageAspdotnet-kishore, following on Google+ Aspdotnet-Kishore, Twitter  on AspdotnetKishore, Linked in Aspdotnet-Kishore, stumbling my posts on stumble upon and subscribing on  RSSfeed Aspdotnet-Kishore for free updates directly to your Email inbox . Watch my blog  for more articles.