Here I will explain difference between cursor and while loop in sql server with example or explain cursor vs while loop with example in sql server. Cursors in sql server allow you to fetch a set of data, loop through each record, and modify the values as necessary; then, you can easily assign these values to variables and perform processing on these values. While loop also same as cursor to fetch set of data and process each row in sql server.

Thank you for reading this post, don't forget to subscribe!

 

Cursor Example

Write cursor script like as shown below and run it. While running the query check execution time in right side

SET NOCOUNT ON
DECLARE ex_cursor CURSOR FOR SELECT OrderID,CustomerID FROM Orders
DECLARE @oid INT
DECLARE @cname NVARCHAR(50)
OPEN ex_cursor
FETCH NEXT FROM ex_cursor INTO @oid,@cname
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT (CAST(@oid AS VARCHAR(5)) + ‘-‘ + @cname)
FETCH NEXT FROM ex_cursor INTO @oid,@cname
END
CLOSE ex_cursor
DEALLOCATE ex_cursor

While Loop Example

Write while loop script like as shown below and run it. While running the query check execution time in right side

DECLARE @Rows INT, @oid INT
DECLARE @cname NVARCHAR(50)
SET @Rows = 1
SET @oid = 0
WHILE @Rows > 0
BEGIN
SELECT TOP 1 @oid = OrderID, @cname = CustomerID FROM Orders WHERE OrderId >= @oid
SET @Rows = @@ROWCOUNT
PRINT (CAST(@oid AS VARCHAR(5)) + ‘-‘ + @cname)
SET @oid += 1
END