SQL Cursor
Using a cursor can be a helpful way to loop through a dataset and complete the same action; whether that be a select, update, delete, print, or more.
Below is an example of a cursor, looping through all tables in the “HumanResource” schema in the “AdventureWorks” database. For each table found, the schema and table name are printed to the message console. There is also text identifying “start”, counter, and “end” printed out. This is an easy way to identify what happens during each loop.
DECLARE @TableName AS NVARCHAR(100)
DECLARE @Counter AS INT
SET @Counter = 0
DECLARE CURSOR_NAME CURSOR FOR
SELECT TABLE_SCHEMA, TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'HumanResources'
OPEN CURSOR_NAME
FETCH NEXT FROM CURSOR_NAME INTO @TableSchema, @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT '--------------------- START -----------------'
PRINT '@Counter: ' + CONVERT(VARCHAR(10), @Counter)
PRINT '@TableSchema: ' + @TableSchema
PRINT '@TableName: ' + @TableName
SET @Counter = @Counter + 1
PRINT '--------------------- END -----------------'
END
CLOSE CURSOR_NAME
DEALLOCATE CURSOR_NAME
SQL Scripts
Lets walk through the script step by step.
1) First we want to declare any variables that we need in the script. Here we are declaring:
@TableSchema & @TableName: TableSchema is a text column and we assign it a nvarchar data type. You could also use varchar. I typically do not use Char for datatypes. They are hard to work with when programming. I am assigning it to 100 characters out of precaution. I would not expect a table name or schema to be longer than 100.
@Counter: Here we declare the counter as an INT because it will be a whole number. We are also setting the @counter to 0 at this point. You can set a counter to anything. I’m using 0 because when I print out the counter, it will tell me which part of the loop it is on. For example, if it prints ‘6’ then I know the loop has gone through 6 times.
Cursor: A cursor always is declared right before the data it will be cursing through. This is why I don’t declare the cursor before we are setting @counter. “CURSOR_NAME” can be any name (without spaces) but this is what you will commonly see. If I have more than one cursor in one script, I will name them appropriately for what the cursor is doing.
DECLARE @TableName AS NVARCHAR(100)
DECLARE @Counter AS INT
SET @Counter = 0
DECLARE CURSOR_NAME CURSOR FOR
2. Select the data. This section you will want to select the data you are cursing through. This can be done by a select statement, as I am or by hard-coding the values. Both ways are shown below. Note- for space and simplicity reasons, only the first 3 tables are hard-coded.
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'HumanResources'
Script Results
UNION SELECT 'HumanResources' AS TABLE_SCHEMA, 'Department' AS TABLE_NAME
UNION SELECT 'HumanResources' AS TABLE_SCHEMA, 'Employee' AS TABLE_NAME
3. After you select the data to cursor through, you have to open the cursor. For each column in your data, you will need a variable in the INTO statement. The variables need to be listed in the same order as the columns in the data being selected.
FETCH NEXT FROM CURSOR_NAME INTO @TableSchema, @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
4. Everything between the BEGIN and END of the cursor will execute for each loop. To see what it looks like after it prints, see the image to the right.
PRINT '--------------------- START -----------------'
PRINT '@Counter: ' + CONVERT(VARCHAR(10), @Counter)
PRINT '@TableSchema: ' + @TableSchema
PRINT '@TableName: ' + @TableName
SET @Counter = @Counter + 1
PRINT '--------------------- END -----------------'
5. The last part of a cursor, is this bit of code. Same cursor name you used above and same INTO statement from above. It is fetching the next record and will continue to do so until @@Fetch_Status <> 0. Lastly, the Cursor is closed.
END
CLOSE CURSOR_NAME
DEALLOCATE CURSOR_NAME