SQL SERVER – Simple Example of Cursor

UPDATE: For working example using AdventureWorks visit : SQL SERVER – Simple Example of Cursor – Sample Cursor Part 2

This is the simplest example of the SQL Server Cursor. I have used this all the time for any use of Cursor in my T-SQL.
DECLARE @AccountID INT
DECLARE
@getAccountID CURSOR
SET
@getAccountID = CURSOR FOR
SELECT
Account_ID
FROM Accounts
OPEN @getAccountID
FETCH NEXT
FROM @getAccountID INTO @AccountID
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT
@AccountID
FETCH NEXT
FROM @getAccountID INTO @AccountID
END
CLOSE
@getAccountID
DEALLOCATE @getAccountID

Reference: Pinal Dave (http://www.SQLAuthority.com), BOL

,
Previous Post
SQL SERVER – Shrinking Truncate Log File – Log Full
Next Post
SQL SERVER – Query to find number Rows, Columns, ByteSize for each table in the current database – Find Biggest Table in Database

Related Posts

196 Comments. Leave new

  • Some indentation would be nice. Leaving your page and googling for something clearer.

    Reply
  • Very good example.. Was of gr8 help. Thanks

    Reply
  • Thanks, nice example

    Reply
  • I want to know what will happen for the below scenario.

    I have done some testing and found that it is a problem.

    Now, I am declaring cursor for table, in a row by row fetch I am inserting a data that qualifies the select statement while declaring cursor.

    For e.g. if my select statement gave me 4 records for salary > 1000 now for each of these 4 record I inserted one record with salary 1000+ in the same table then the records inserted newly should be 4. so the total records for salary > 1000 should be 8 after the loop ends.

    But this is not the case. The loop executes for random numbers in my case 886 to 122122 times.

    Please suggest how to make sure Rows selected for cursor for the first time do not change with example.

    Thanks in advance

    Reply
  • Very good example it is i found for a cursor..

    Reply
  • Hi sir,
    i want to know what is the difference between a procedure, function and cursor theoritically…

    thanks in advance.

    Reply
  • Msg 137, Level 15, State 2, Line 13
    Must declare the scalar variable “@cursor”.
    Msg 137, Level 15, State 2, Line 15
    Must declare the scalar variable “@cursor”.
    Msg 137, Level 15, State 2, Line 16
    Must declare the scalar variable “@cursor”.

    this was the msg which i was getting at the time of execution

    Reply
  • Thanks for teaching me cursor very simply…..

    Reply
  • hi

    i want to select the tables which are seeded from database creation.can any one help to create it using cursors

    seed tables means the tables which have the default data when a database is created

    please tell me its urgent

    Reply
  • Jaymin Soneji
    May 24, 2013 9:05 pm

    what ‘s the use of Insensitive keyword in cursor ?

    Reply
  • Excellent explanation..

    Reply
  • Dear friends,
    Please Help me to solve this.
    From cursor 4 Rows are returned.
    But I need that 4 rows concatenated as a single row.

    Thank you

    Reply
  • Vijay Vishwakarma
    June 26, 2013 12:16 pm

    Dear sir ,
    Please help me ,
    How i get the complete level of Binary tree ,Ex. Suppose in binary tree it complete the pair then its level is 1 , it its child complete the pair both child then parent node complete the level 2 and child node complete the level 1 same as apply for every node .
    The main thing is the i create a prco in which pass the node id get the completed level of passing node

    EXEC spGetLevel(1)
    Ans is 2 means total pair is 3 1 for patent node and 2 pair for child node

    Please Help me …..

    Reply
  • Thanks, it’s a very usefull example

    Reply
  • Thanks, it work perfectly for my situation.

    Reply
  • Vijay Mane(DBA)
    February 14, 2014 4:31 pm

    To avoid cursors in sql server we can do it by below simple scenario..

    –create temporary table with same structure of its original table
    Create table #temp_table_name(id bigint primary key identity(1,1),[other parametrs that you want to include here….] )

    insert into #temp_table_name( parametrs that you want to include here….)
    select [parameters include here which you are going to processed]
    FROM Original_table_name

    –replace your cursor logic by below scenario….
    SELECT @MINROWID=MIN(ID),@MAXROWID=MAX(ID) FROM #temp_table_name WITH(NOLOCK) ;

    SET @CURRROWID=@MINROWID;

    WHILE (@CURRROWID<=@MAXROWID)
    BEGIN

    ——you will get one row at a time here..
    SELECT @parameters1,@para2……..n
    FROM #temp_table_name WHERE ID=@CURRROWID
    set @CURRROWID=@CURRROWID+1

    END

    Reply
  • Hi, Would you be able to help me with the below example:
    I have 3 tables with columns:
    Table 1: Orders
    columns: order_id; cancelled_flag;
    Table 2: Orders_details
    columns: order_id; product_id; quantity;
    Table 3: Products
    columns: product_id; amount_inventory;

    If the flag in the Table 1 is set to “Y” the trigger or cursor should check what were the amounts ordered in Table 2 and add these amounts back to the appropriate products in Table 3. There is an option that there are several products ordered under one order id therefore I suppose I need a cursor.
    I would be gratefull for your help.
    Thank you,

    Reply
  • can every1 help me, i want to set null when cursor row position 1, the result fetch cursor llike this :
    col1 col2
    A b1
    A b2
    i want
    A b1
    null b2
    is there commad to check rows when position 1 . to set null?

    Reply
  • Pritesh vegad
    June 25, 2016 11:35 am

    thank you

    Reply
  • Hi All,
    Can any one help me out from this problem.

    i want to copy bulk of tables not the data from one database to another database using cursor.
    i had written but its throngs error.

    declare c1 cursor for select name from sys.objects where type=’U’
    declare @name varchar(20)
    open c1
    fetch next from c1 into @name
    while @@FETCH_STATUS=0
    begin
    select * into db1.dbo.@name from db2.dbo.@name where 1=2
    fetch next from c1 into @name
    end
    close c1
    deallocate c1

    Reply

Leave a Reply