SQL Server Cursors

There are times when using cursors to process something in SQL is the right thing to do. These are the steps that I take when considering if cursors are the right thing to use and the process for using them:

  1. Are you really sure you can’t do the operation using a regular query?
  2. Are you processing something across a single or multiple databases?
  3. Are you processing something across single or multiple tables?
  4. Do the tables you want to process against have the same structure or do you have some metadata that describes how you want your process to work?
The example below is for a couple of stored procedures that, when combined together, allow you to find a string in any string column of any table in the database that you are working in.
The sections of a cursor:
  1. The Declaration and Name
    1. Pretty Simple. Make sure you don’t use the same name as another cursor on the server, as cursor declarations are global.
  2. The Open
  3. The Processing Loop
    1. Most other examples you will see with cursors will use a while loop. I use an if condition and a goto here for a couple of reasons.
      1. It reduces the risk that I will write an infinite loop by forgetting to add the second fetch statement (which is required in the while loop).
      2. It means I only have to change the fetch statement in one location if I change the definition of the cursor.
  4. The Close
  5. The Deallocation
use AdventureWorks2016CTP3
go

if object_id('dbo.FindString') is not null 
 drop procedure dbo.FindString
if object_id('dbo.ForEachTable') is not null
 drop procedure dbo.ForEachTable
go
create procedure dbo.ForEachTable 
 @sqlQuery nvarchar(max), 
 @paramDef nvarchar(255), 
 @Param nvarchar(255)
as begin
 set nocount on;
 set xact_abort on;
/*1. The Declaration and Name*/
 declare Find_StringCursor cursor for
  select
   c.TABLE_SCHEMA
     ,c.TABLE_NAME
     ,c.COLUMN_NAME
  from INFORMATION_SCHEMA.COLUMNS c
  join INFORMATION_SCHEMA.TABLES t
   on t.TABLE_SCHEMA = c.TABLE_SCHEMA
    and t.TABLE_NAME = c.TABLE_NAME
  where t.TABLE_TYPE = 'Base Table'
  and c.DATA_TYPE like '%char%' ;
/*2. The Open*/
 open Find_StringCursor;
 declare @TableSchema nvarchar(255);
 declare @TableName nvarchar(255);
 declare @ColumnName nvarchar(255);
 declare @sqlstring nvarchar(max);
 create table #Results(
  Table_Schema nvarchar(255),
  Table_Name nvarchar(255),
  Column_Name nvarchar(255),
  RecordCount int
 )
/*3. The Processing Loop*/
fetch_nexxt_Find_String_Cursor:
 fetch next from Find_StringCursor into @TableSchema, @TableName, @ColumnName
 if @@fetch_status != -1 begin
  set @sqlstring = @sqlQuery;
  set @sqlString = replace(@sqlString, '<Table_Schema>', quotename(@TableSchema));
  set @sqlString = replace(@sqlString, '<Table_Name>', quotename(@TableName));
  set @sqlString = replace(@sqlString, '<Column_Name>', quotename(@ColumnName));
  begin try 
   insert into #Results
   exec sp_executesql @sqlString, @paramDef, @Param;
  end try 
  begin catch 
   print error_message();
   print @sqlString
  end catch 
  goto fetch_nexxt_Find_String_Cursor
 end
/*4. The Close*/
 close Find_StringCursor
/*5. The deallocation*/
 deallocate Find_StringCursor

 select *
 from #Results
 where RecordCount != 0;
end 
go
create procedure dbo.FindString
 @SearchValue nvarchar(255)
as begin
 set nocount on;
 set xact_abort on;
 declare @sqltemplate nvarchar(max) = '
  select 
   ''<Table_Schema>'' as ts, 
   ''<Table_Name>'' as tn, 
   ''<Column_Name>'' as cn,
   count(*) as r
  from <Table_Schema>.<Table_Name> a
  where <Column_Name> like @Value
 ';
 
 declare @ParamDef nvarchar(255) = '@Value nvarchar(255)'

 exec dbo.ForEachTable @sqlTemplate, @ParamDef, @SearchValue;
end
go
exec FindString 'John%';