Thursday, 6 February 2014

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 =--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.

No comments:

Post a Comment

© 2012-2014 Aspdotnet-Kishore.blogspot.com. All Rights Reserved.
The content is copyrighted to Kishore and may not be reproduced on other websites without permission from the owner.