In SQL Server, we can use While loop to iterate using a Variable. But if we want to iterate through the rows of a result set, we can go for Cursors.
So Cursors is a mechanism to explicitly enumerate through the rows of a result set, rather than retrieving it as it is.
Syntax of a Cursor is,
DECLARE [YOUR CURSOR NAME] CURSOR FOR [USE YOUR SELECT STATEMENT HERE] OPEN [YOUR CURSOR NAME] FETCH NEXT FROM [CURSOR NAME] WHILE (@@Fetch_Status = 0) BEGIN [Do Manipulation for every Row Here] END CLOSE [YOUR CURSOR NAME] DEALLOCATE [YOUR CURSOR NAME]
Now we are going to see an example for CURSOR with better usage:
Consider this scenario. I have a 17 tables in my database with similar name %TenantSummary% like
I want to insert similar set of values in all these 17 tables. For this, We can create individual Insert statements.
But I am going to simplify this by Creating a Cursor that will insert similar set of values in all these 17 tables.
Following is the code that will insert similar set of values in all the TenantSummary tables.
DECLARE @TableName varchar(200) DECLARE @Query varchar(Max) DECLARE TenantSummary CURSOR FOR SELECT '[' + schemas.name + '].[' + tables.name + ']' FROM sys.tables INNER JOIN sys.schemas ON schemas.schema_id = tables.schema_id WHERE tables.Name LIKE '%TenantSummary' OPEN TenantSummary FETCH NEXT FROM TenantSummary into @TableName WHILE (@@Fetch_Status = 0) BEGIN SET @Query = 'INSERT INTO ' + @TableName + ' SELECT ID, Name, 1 FROM Master WHERE ID LIKE ''%DTS%'' GROUP BY ID ' PRINT @Query EXEC(@Query) FETCH NEXT FROM TenantSummary into @TableName END CLOSE TenantSummary DEALLOCATE TenantSummary
But always there is a saying that Cursors will cause severe Performance issues in SQL. So limit the usage
of Cursors. But for QA, if we are not bothered about some extra seconds of execution for SQL Queries, We can do magics using Cursors in SQL.