Simple cursor in SQL Server

A cursor is a way to iterate each row one by one in a SQL Server table. Although, T-SQL is a set based language however, many a times need arises to do row by row operations. Below is a simple cursor in SQL Server to select values from a table.

--simple cursor in sql server 
Declare @orderid int, @orderqty int
-- declare a cursor
DECLARE sample_cursor CURSOR FOR 
SELECT SalesOrderId,OrderQty from Sales.SalesOrderDetail

-- open cursor and fetch first row into variables
OPEN sample_cursor
FETCH NEXT FROM sample_cursor into @orderid,@orderqty

-- check for a new row
WHILE @@FETCH_STATUS=0
BEGIN
-- do complex operation here
PRINT 'Order ID: ' + CAST(@orderid as varchar(12)) + ' Order Qty: ' + CAST(@orderqty as varchar(12))
-- get next available row into variables
FETCH NEXT FROM sample_cursor into @orderid,@orderqty 
END
close sample_cursor
Deallocate sample_cursor

1_simple cursor in sql server

The query declares a sample_cursor to iterate through Sales.SalesOrderDetail table. The @@Fetchstatus function returns the status of the last cursor FETCH against a cursor, where 0 means successful, -1 is failed or row is not in result set and -2 means that row fetched is not present in the cursor.  Thus, we can iterate through a cursor while @@Fetchstatus =0. The complex operation is done inside the while loop. The example shown here is just to explain the cursor; it doesn’t perform any complex operation.

 
Like us on FaceBook Join the fastest growing SQL Server group on FaceBook

Data Platform Virtual Summit 2020

Subscribe to SQLServerGeeks YouTube channel. If you want more learning content in your inbox, subscribe to SQLServerGeeks Bulletin.

SQLServerGeeks YouTube | SQLServerGeeks Bulletin | SQLServerGeeks Twitter

Leave a Reply

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