October 6, 2016 at 1:57 am
In SQLServer CURSORs are declared as STATIC and DYNAMIC, what criteria was taken under consideration to convert the CURSORs from SYBASE to SQLSERVER as STATIC and DYNAMIC because in SYBASE CURSORs are declared without specific keywords for static and dynamic.
For example
1) STATIC CURSOR CONVERSION:
In SYBASE procedure the CURSOR is declared as
DECLARE cursor_name CURSOR FOR ....
----
In SQLSERVER CURSOR is Declared as STATIC.
DECLARE
Cursor_name CURSOR LOCAL FORWARD_ONLY STATIC FOR ....
-------------------------------------------------
2) DYNAMIC CURSOR Conversion:
In SYBASE CURSOR is Declared as
DECLARE hist CURSOR FOR .....
---
In SQLServer CURSOR Declared as
DECLARE
Cursor_name CURSOR LOCAL FORWARD_ONLY DYNAMIC FOR
-------------------------------------------------
What criteria were taken under consideration to convert the CURSORs from SYBASE to SQLSERVER as STATIC and DYNAMIC ?
Were in-flight transactions considered for the CURSORs declared as static?
October 6, 2016 at 4:51 am
Although SQL has feature to define the type of cursor as you have mentioned. But it's not compulsion to define it.
You can simply use the following syntax to define the cursor equivalent to your sybase cursor =
DECLARE vend_cursor CURSOR
FOR SELECT * FROM Test
OPEN vend_cursor
FETCH NEXT FROM vend_cursor;
Don't forget to CLOSE & DEALLOCATE the cursor using the following syntax once you are done with it.
CLOSE vend_cursor;
DEALLOCATE vend_cursor;
October 6, 2016 at 8:28 am
Even better than converting it would be to eliminate it entirely. Cursors are horrible for performance and far too often they are used inappropriately. They have their place but if you are doing DML it is highly likely that a simpler and faster set based approach can replace the cursor.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply