Microsoft_SQL_Server

Alias Names in SQL ServerBasic DDL Operations in MS SQL ServerDynamic SQLDynamic SQL PivotInstalling SQL Server on WindowsJSON in SQL ServerManaging Azure SQL DatabaseMicrosoft SQL Server Advanced optionsMicrosoft SQL Server Aggregate FunctionsMicrosoft SQL Server Analyzing a QueryMicrosoft SQL Server Backup and Restore DatabaseMicrosoft SQL Server bcp (bulk copy program) UtilityMicrosoft SQL Server BULK ImportMicrosoft SQL Server CASE StatementMicrosoft SQL Server CLUSTERED COLUMNSTOREMicrosoft SQL Server COALESCEMicrosoft SQL Server Common Language Runtime IntegrationMicrosoft SQL Server Common Table ExpressionsMicrosoft SQL Server Computed ColumnsMicrosoft SQL Server Converting data typesMicrosoft SQL Server CREATE VIEWMicrosoft SQL Server cross applyMicrosoft SQL Server CursorsMicrosoft SQL Server Data TypesMicrosoft SQL Server Database permissionsMicrosoft SQL Server Database SnapshotsMicrosoft SQL Server DatesMicrosoft SQL Server DBCCMicrosoft SQL Server DBMAILMicrosoft SQL Server Delimiting special characters and reserved wordsMicrosoft SQL Server Drop KeywordMicrosoft SQL Server Dynamic data maskingMicrosoft SQL Server EncryptionMicrosoft SQL Server Export data in txt file by using SQLCMDMicrosoft SQL Server File GroupMicrosoft SQL Server FilestreamMicrosoft SQL Server for JSONMicrosoft SQL Server for XML PATHMicrosoft SQL Server Foreign KeysMicrosoft SQL Server Full-Text IndexingMicrosoft SQL Server Generating a range of datesMicrosoft SQL Server GROUP byMicrosoft SQL Server IF...ELSEMicrosoft SQL Server In-Memory OLTP (Hekaton)Microsoft SQL Server IndexMicrosoft SQL Server InsertMicrosoft SQL Server INSERT INTOMicrosoft SQL Server IntroductionMicrosoft SQL Server Isolation levels and lockingMicrosoft SQL Server JoinMicrosoft SQL Server Last Inserted IdentityMicrosoft SQL Server Limit Result SetMicrosoft SQL Server Logical FunctionsMicrosoft SQL Server Management Studio Shortcut KeysMicrosoft SQL Server MERGEMicrosoft SQL Server MigrationMicrosoft SQL Server Modify JSON textMicrosoft SQL Server Move and copy data around tablesMicrosoft SQL Server Natively compiled modules (Hekaton)Microsoft SQL Server NULLsMicrosoft SQL Server OPENJSONMicrosoft SQL Server ORDER byMicrosoft SQL Server over ClauseMicrosoft SQL Server PaginationMicrosoft SQL Server ParsenameMicrosoft SQL Server PartitioningMicrosoft SQL Server Permissions and SecurityMicrosoft SQL Server PHANTOM readMicrosoft SQL Server PIVOT / UNPIVOTMicrosoft SQL Server Primary KeysMicrosoft SQL Server Privileges or PermissionsMicrosoft SQL Server Queries with JSON dataMicrosoft SQL Server Query HintsMicrosoft SQL Server Query StoreMicrosoft SQL Server Querying results by pageMicrosoft SQL Server Ranking FunctionsMicrosoft SQL Server Resource GovernorMicrosoft SQL Server Retrieve information about the databaseMicrosoft SQL Server Retrieve Information about your InstanceMicrosoft SQL Server Row-level securityMicrosoft SQL Server Scheduled Task or JobMicrosoft SQL Server SchemasMicrosoft SQL Server SCOPE IDENTITY()Microsoft SQL Server SELECT statementMicrosoft SQL Server SequencesMicrosoft SQL Server Service brokerMicrosoft SQL Server Sorting/ordering rowsMicrosoft SQL Server Spatial DataMicrosoft SQL Server SQLCMDMicrosoft SQL Server Stored Procedures



Microsoft SQL Server Cursors

From WikiOD

Syntax[edit | edit source]

  • DECLARE cursor_name CURSOR [ LOCAL | GLOBAL ]
    • [ FORWARD_ONLY | SCROLL ]

[ STATIC | KEYSET | DYNAMIC | FAST_FORWARD ]

[ READ_ONLY | SCROLL_LOCKS | OPTIMISTIC ]

[ TYPE_WARNING ]

    • FOR select_statement
    • [ FOR UPDATE [ OF column_name [ ,...n ] ] ]

Remarks[edit | edit source]

Normally you would want to avoid using cursors as they can have negative impacts on performance. However in some special cases you may need to loop through your data record by record and perform some action.

Basic Forward Only Cursor[edit | edit source]

Normally you would want to avoid using cursors as they can have negative impacts on performance. However in some special cases you may need to loop through your data record by record and perform some action.

DECLARE @orderId AS INT
** here we are creating our cursor, as a local cursor and only allowing 
** forward operations
DECLARE rowCursor CURSOR LOCAL FAST_FORWARD FOR
    -- this is the query that we want to loop through record by record
    SELECT [OrderId]
    FROM [dbo].[Orders]
** first we need to open the cursor
OPEN rowCursor
** now we will initialize the cursor by pulling the first row of data, in this example the [OrderId] column,
** and storing the value into a variable called @orderId
FETCH NEXT FROM rowCursor INTO @orderId
** start our loop and keep going until we have no more records to loop through
WHILE @@FETCH_STATUS = 0 
BEGIN

    PRINT @orderId

    -- this is important, as it tells SQL Server to get the next record and store the [OrderId] column value into the @orderId variable
    FETCH NEXT FROM rowCursor INTO @orderId

END
** this will release any memory used by the cursor
CLOSE rowCursor
DEALLOCATE rowCursor

Rudimentary cursor syntax[edit | edit source]

A simple cursor syntax, operating on a few example test rows:

/* Prepare test data */
DECLARE @test_table TABLE
(
    Id INT,
    Val VARCHAR(100)
);
INSERT INTO @test_table(Id, Val)
VALUES 
    (1, 'Foo'), 
    (2, 'Bar'), 
    (3, 'Baz');
/* Test data prepared */

/* Iterator variable @myId, for example sake */
DECLARE @myId INT;

/* Cursor to iterate rows and assign values to variables */
DECLARE myCursor CURSOR FOR
    SELECT Id
    FROM @test_table;

/* Start iterating rows */
OPEN myCursor;
FETCH NEXT FROM myCursor INTO @myId;

/* @@FETCH_STATUS global variable will be 1 / true until there are no more rows to fetch */
WHILE @@FETCH_STATUS = 0
BEGIN

    /* Write operations to perform in a loop here. Simple SELECT used for example */
    SELECT Id, Val
    FROM @test_table 
    WHERE Id = @myId;

    /* Set variable(s) to the next value returned from iterator; this is needed otherwise the cursor will loop infinitely. */
    FETCH NEXT FROM myCursor INTO @myId;
END
/* After all is done, clean up */
CLOSE myCursor;
DEALLOCATE myCursor;

Results from SSMS. Note that these are all separate queries, they are in no way unified. Notice how the query engine processes each iteration one by one instead of as a set.

Id Val
1 Foo
(1 row(s) affected)
Id Val
2 Bar
(1 row(s) affected)
Id Val
3 Baz
(1 row(s) affected)

Credit:Stack_Overflow_Documentation