Cursors in SQL Server and its Usage:

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
1. AppTenantSummary
2. ClientTenantSummary
3. RowTenantSummary
.
.
.
.
.
17. DataTenantSummary

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.

Advertisement