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