Main menu

MSSQL Developement: TSQL cursor tutorial with example

What is cursor: In TSQL cursor process the one row at a time in set of records.

Let’s check sysntex for detailed information .

-- Declare cursor variable and store set of records on it.

DECLARE [CursorName] CURSOR FOR [SelectStatement ]

-- Open Cursor
OPEN [CursorName]

	-- Global variable to check Cursor is empty or not
	WHILE @@FETCH_STATUS = 0
	BEGIN
		-- Your Operations 
	END

-- Close Cursor 
CLOSE [CursorName]

Let’s extend the above syntex and add some fetch functionality in it.  Fetch values from cursor and store then in a variable.

-- Declare some varibales
DECLARE @id INT
DECLARE	@name	VARCHAR(max)

-- Delclare cursor varibale and store set of records on it.
DECLARE [CursorName] CURSOR FOR [SelectStatement ]

-- Open Cursor
OPEN [CursorName]

-- Fetching the first record from Cursor and store them in varibales
FETCH NEXT FROM CursorName INTO @ID, @Name

	-- Global varibale to check Cursor is empty or not
	WHILE @@FETCH_STATUS = 0
	BEGIN
		-- Fetching the first record from Cursor and store them in varibales
		FETCH NEXT FROM CursorName INTO @ID, @Name
	END
	
-- Close Cursor 
CLOSE [CursorName]
-- Unload cursor from memory. 
DEALLOCATE [CursorName]

Example 1

-- Declare some variables
DECLARE @id INT
DECLARE	@Name	VARCHAR(max)

-- Declare cursor varibale and store set of records on it.
DECLARE dbUsersIT CURSOR FOR select id, name from dbUsers where department='it';

-- Open Cursor
OPEN dbUsersIT

-- Fetching the first record from Cursor and store them in variables
FETCH NEXT FROM dbUsersIT INTO @ID, @Name

	-- Global variable to check Cursor is empty or not
	WHILE @@FETCH_STATUS = 0
	BEGIN
		-- Fetching the first record from Cursor and store them in variables
		FETCH NEXT FROM dbUsersIT INTO @ID, @Name
		PRINT @Name
	END
	
-- Close Cursor 
CLOSE dbUsersIT
-- Unload cursor from memory. 
DEALLOCATE dbUsersIT

 

FacebookTwitterGoogle+RSS