Cursor (SQL Server)

There is something missing when my first experience in creating T-SQL query, especially when I have to loop and process query result one by one with different conditions (for JAVA programmer usually will iterate “ResultSet” object). So, when I search in internet, I found Cursor Clause.

The following example:

[code language=”sql”]
DECLARE @portfolio VARCHAR(100)
DECLARE @bi_acct_tp VARCHAR(100)

DECLARE db_cursor CURSOR
FOR
        SELECT  *
        FROM    (
                  SELECT    ‘arif’ AS NAME , 1 AS amount
                  UNION ALL
                  SELECT    ‘arif’ AS NAME , 2 AS amount
                  UNION ALL
                  SELECT    ‘arif’ AS NAME , 3 AS amount
                  UNION ALL
                  SELECT    ‘nasution’ AS NAME , 4 AS amount
                  UNION ALL
                  SELECT    ‘nasution’ AS NAME , 5 AS amount
                ) a

OPEN db_cursor   
FETCH NEXT FROM db_cursor INTO @portfolio, @bi_acct_tp   

WHILE @@FETCH_STATUS = 0 
      BEGIN   
            PRINT @portfolio + ‘ ‘ + @bi_acct_tp
            FETCH NEXT FROM db_cursor INTO @portfolio, @bi_acct_tp    
      END   

CLOSE db_cursor   
DEALLOCATE db_cursor
[/code]

 

From above query, you have to notice:

  • Declare <cursor_name> Cursor FOR <your_query> : this is how you initialize cursor.
  • Open <cursor_name> : for first initialization, cursor stream is closed, so you have to open it.
  • Fetch <options> : in this example, I used “NEXT”. (for detail options, you can read documentation J).
  • Fetch NEXT from <cursor_name> into <var1>,… : you put query result one by one to destination variable. For the length of variable, it must
  • Close <cursor_name> : it’s like you close stream cursor.
  • Deallocate <cursor_name> : remove your cursor. If you don’t do this, your cursor will be stayed in memory until the end of your query session.

Additional information from me, using Cursor is not recommended to be used actually because it’s really consume memory, so, the solution is you have to pay attention for memory or you can use while clause.

I hope this help.

Marifnst, 2013-08-12

Leave a Reply

Your email address will not be published. Required fields are marked *

Afiseaza emoticoanele Locco.Ro