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