SQL Cursor to use Table and Field Names from Temp Table

Question

I'll preface this by letting you all know that I promised myself a few years ago never to use a cursor in SQL where it's not needed. Unfortunately I think I may have to use one in my current situation but it's been so long that I'm struggling to remember the correct syntax.

Basically, I've got a problem with CONVERT_IMPLICIT happening in queries because I have data types that are different for the same field in different tables so I'd like to eventually convert these to int. But to do this I need to check whether all data can be converted to int or not to see how big the job is.

I've got the query below which gives me a list of all tables in the database that contain the relevant field in a list;

IF OBJECT_ID('tempdb..#BaseData') IS NOT NULL DROP TABLE #BaseData
GO
CREATE TABLE #BaseData (Table_Name varchar(100), Field_Name varchar(100), Data_Type_Desc varchar(20), Data_Max_Length int, Convertible bit)

DECLARE @FieldName varchar(20); SET @FieldName = 'TestFieldName'

INSERT INTO #BaseData (Table_Name, Field_Name, Data_Type_Desc, Data_Max_Length)
SELECT
o.name ,c.name ,t.name ,t.max_length 
FROM sys.columns c
JOIN sys.types t
    ON c.user_type_id = t.user_type_id
JOIN sys.objects o
    ON c.object_id = o.object_id
WHERE c.name LIKE '%' + @FieldName + '%'
    AND o.type_desc = 'USER_TABLE'

Which gives results like this;

Table_Name  Field_Name      Data_Type_Desc  Data_Max_Length Convertible
Table1      TestFieldName   varchar         8000            NULL
Table2      TestFieldName   nvarchar        8000            NULL
Table3      TestFieldName   int             4               NULL
Table4      TestFieldName   varchar         8000            NULL
Table5      TestFieldName   varchar         8000            NULL

What I'd like to do is to check if all data in the relevant table & field can be converted to an int and update the 'convertible' field (1 if there's data that can't be converted, 0 if the data is fine). I've got the following calculation which works perfectly fine;

'SELECT  
CASE 
    WHEN COUNT(' + @FieldName + ') - SUM(ISNUMERIC(' + @FieldName + ')) > 0 
        THEN 1 
    ELSE 0 
END 
FROM ' + @TableName

And gives the result that I'm after. But I'm struggling to get to the correct syntax to create the cursor which will look at each row in my temp table and run this SQL accordingly. It then needs to update the final column of the temp table with the result of the query (1 or 0).

This will have to be run on a couple of hundred databases which is why I need this list to be dynamic, there may well be custom tables in some databases (in fact, it's pretty likely).

If anybody could give any guidance it would be greatly appreciated.

Thanks

Answer 1

I made a couple of changes to your original query but here is something that should work. I have done similar things in the past :-)

Changes:

• Added schema to the source table - my test database had matches in multiple schemas

• Changed datatypes to sysname, smallint to match table definitions or names could get truncated

  IF OBJECT_ID('tempdb..#BaseData') IS NOT NULL DROP TABLE #BaseData;
  GO
  
  CREATE TABLE #BaseData (Schema_Name sysname, Table_Name sysname, Field_Name sysname, Data_Type_Desc sysname, Data_Max_Length smallint, Convertible bit);
  
  DECLARE @FieldName varchar(20); SET @FieldName = 'TestFieldName';
  
  INSERT INTO #BaseData (Schema_Name, Table_Name, Field_Name, Data_Type_Desc, Data_Max_Length)
  SELECT
  s.name, o.name ,c.name ,t.name ,t.max_length 
  FROM sys.columns c
  JOIN sys.types t
      ON c.user_type_id = t.user_type_id
  JOIN sys.objects o
      ON c.object_id = o.object_id
  JOIN sys.schemas s ON o.schema_id=s.schema_id
  WHERE c.name LIKE '%' + @FieldName + '%'
      AND o.type_desc = 'USER_TABLE';
  
  --select * from #BaseData;
  
  DECLARE @sName sysname,
          @tName sysname,
          @fName sysname,
          @sql VARCHAR(MAX);
  
  DECLARE c CURSOR LOCAL FAST_FORWARD FOR
      SELECT  Schema_Name,
              Table_Name,
              Field_Name
      FROM #BaseData;
  OPEN c;
  FETCH NEXT FROM c INTO @sName, @tName, @fName;
  
  WHILE @@FETCH_STATUS = 0
  BEGIN
  SET @sql = 'UPDATE #BaseData SET Convertible =   
                  (SELECT 
                  CASE 
                      WHEN COUNT(' + @fName + ') - SUM(ISNUMERIC(' + @fName + ')) > 0 
                          THEN 1 
                      ELSE 0
                  END Convertible
                  FROM ' + @sName + '.' + @tName + ')
              FROM #BaseData WHERE Schema_Name = ''' + @sName + ''' AND Table_Name = ''' + @tName + ''' AND Field_Name = ''' + @fName + '''';
  
  --select @sql;
  EXEC(@sql); 
  
  FETCH NEXT FROM c INTO @sName, @tName, @fName;
  END
  
  CLOSE c;
  DEALLOCATE c;
  
  select *
  from #BaseData;
  

Answer 2

If I understand your question, I would do something like this to identify those records that do not cast as an [int].

You didn't state which version of SQL Server you're using; TRY_CAST and TRY_CONVERT are 2012 or later.

DECLARE @test AS TABLE ( [field] [sysname] );

INSERT INTO @test ( [field] ) VALUES ( N'1' ), ( N'a' );

SELECT [field] FROM @test WHERE TRY_CAST([field] AS [INT]) IS NULL;

-- this is the basic sql syntax for a cursor CURSOR (https://msdn.microsoft.com/en-us/library/ms180169.aspx)

  DECLARE @parameter [sysname];
  BEGIN
        DECLARE [field_cursor] CURSOR
        FOR
                SELECT  [value]
                FROM    [<schema>].[<table>];

        OPEN [field_cursor];

        FETCH NEXT FROM [field_cursor] INTO @parameter;

        WHILE @@FETCH_STATUS = 0
              BEGIN
                    -- do something really interesting here

                    FETCH NEXT FROM [field_cursor] INTO @parameter;
              END;

        CLOSE [field_cursor];

        DEALLOCATE [field_cursor];
  END;