Updated June 8, 2023
Introduction to T-SQL cursor
T-SQL cursor is a set of logic that can be used for looping over a pre-established number of rows, one row can be processed at a time which we can say that it can process a dataset on a row by row basis, the cursor in the database is the object that allows for traversing over the rows of a result set, it also allows us to exercises independent row which has been given back by a query, the main motive of the cursor is to update a row at one time, it can also be useful in development, DBA, and ETL processes.
What is T-SQL cursor?
The T-SQL cursor is the T-SQL logic that can be utilized for looping between the related query results, it allows us to perform actions sequentially like it performs an update action on a single row, in which we can say that the cursor in the database is working as an object which can traverse the data over the rows of a result set, it can use the SELECT statement for getting back a set of rows which is a result set.
Any operation in a database can be performed on the whole set of rows, in which the group of rows can be given by a SELECT statement containing all the rows that can fulfill the WHERE clause condition, so cursors can exercise by allowing the locating at the particular row of the result set, it also can able to retrieve one row or set of a row from the ongoing position of the result set.
Uses of T-SQL cursor
- First declare a cursor,
DECLARE cursor_name CURSOR
FOR select_statement;- Use code to open and populate the cursor,
OPEN cursor_name;- Then the row can fetch the cursor to the variables,
FETCH NEXT FROM cursor into variable_list;- It provides a function as ‘@@FETCHSTATUS’ which gives back the status of the last cursor in which the FETCH statement has been carried out in opposition to the cursor, if the ‘@@FETCHSTATUS’ gets ‘0’ then the ‘FETCH’ statement is successful.
WHILE@@FETCH_STATUS = 0
BEGIN
FETCH NEXT FROM cursor_name;
END;- Then the cursor can be closed as given below,
CLOSE cursor_name;- At last, deallocation of the cursor can be done as shown below,
DEALLOCATE cursor_name;T-SQL server cursor
The T-SQL server cursor has been used to loop into the related query result that allows us to take efforts serially for modification on a single row, in which the server cursor is a group of T-SQL logic that is pre-determined that can process one row at a time, these cursors can be used for development, DBA, and ETL processes, to generate the server cursor is the accordant process,
- We can able to declare the variables with database names, file names, and account numbers, for that we need to have logic and initialization of the variables.
- We can able to declare variables with a particular name as db_cursor which can be used in the logic using SELECT statement, and the cursor has been needful for populating the record, cursor name can be anything.
- To fetch the record from the cursor for starting the data processing we need to have the same number of variables declared for the cursor in which columns can be in the SELECT statement and variables have been used for fetching the logic.
- The processing of data like insertion, and deletion, can be unique for every group of logic which can be the major set of logic while processing every row.
- For fetching the succeeding record from the cursor we need to perform steps 3 and step 4 can be repeated once more by filtering the selected data.
- At one time when all data has been processed then we can close the cursor.
- After that, we need to deallocate the cursor for freeing all the internal resources.
Example #1
Let us see an example of querying the database for getting information on the first tables which are ordered by their name,
DECLARE @table_name VARCHAR(128);
DECLARE @table_names_5 VARCHAR(128);
DECLARE cursor_table_names CURSOR FOR
SELECT TOP 5 TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
ORDER BY TABLE_NAME ASC;
SET @table_names_5 = 'first 5 tables are: '
OPEN cursor_table_names;
FETCH NEXT FROM cursor_table_names INTO @table_name;
WHILE @@FETCH_STATUS = 0
BEGIN
IF @table_names_5 = 'first 5 tables are: '
SET @table_names_5 = CONCAT(@table_names_5, @table_name)
ELSE
SET @table_names_5 = CONCAT(@table_names_5, ', ', @table_name);
FETCH NEXT FROM cursor_table_names INTO @table_name;
END;
PRINT @table_names_5;
CLOSE cursor_table_names;
DEALLOCATE cursor_table_names;Output:
Example #2
Let us see an example of the table given below from the sample database to understand how to use a cursor,
- At first, we have to declare two variables to detain the product name, and list price, then cursor for detaining the query, that can get the product name and list price from the given table,
DECLARE
@product_name VARCHAR(MAX),
@list_price DECIMAL;
DECLARE cursor_product CURSOR
FOR SELECT
product_name,
list_price
FROM
production.products;- Then cursor can be open as,
OPEN cursor_product;- Each row has been fetched from the cursor and gives out the product name and price list,
FETCH NEXT FROM cursor_product INTO
@product_name,
@list_price;
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT @product_name + CAST(@list_price AS varchar);
FETCH NEXT FROM cursor_product INTO
@product_name,
@list_price;END;
- After that, the cursor has been closed.
CLOSE cursor_product;- In the end the deallocation of the cursor for freeing the data.
DEALLOCATE cursor_product;- Above all steps combined together in this step as given below,
DECLARE
@product_name VARCHAR(MAX),
@list_price DECIMAL;
DECLARE cursor_product CURSOR
FOR SELECT
product_name,
list_price
FROM
production.products;
OPEN cursor_product;
FETCH NEXT FROM cursor_product INTO
@product_name,
@list_price;
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT @product_name + CAST(@list_price AS varchar);
FETCH NEXT FROM cursor_product INTO
@product_name,
@list_price;
END;
CLOSE cursor_product;
DEALLOCATE cursor_product;The output will be.
Conclusion
In this article we conclude that the T-SQL cursor has been used for processing a set of rows in which it can process one row at a time, we have also learned the use of the cursor, and server cursor, so this article will help us to understand the concept of T-SQL cursor.
Recommended Articles
This is a guide to T-SQL cursor. Here we discuss the Introduction, What is T-SQL cursor, Use of T-SQL cursor, examples with code implementation. You may also have a look at the following articles to learn more –
