SQL Cursor使用Temp Table中的表和字段名称

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.

我将通过让大家知道我几年前向自己承诺永远不会在不需要它的SQL中使用游标来开头。不幸的是,我认为我可能不得不在目前的情况下使用一个,但是很长时间以来我都在努力记住正确的语法。

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.

基本上,我在查询中遇到CONVERT_IMPLICIT问题,因为我的数据类型对于不同表中的相同字段是不同的,所以我想最终将它们转换为int。但要做到这一点,我需要检查是否所有数据都可以转换为int,以查看作业有多大。

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;

我想做的是检查相关表和字段中的所有数据是否都可以转换为int并更新'convertible'字段(如果有无法转换的数据则为1,如果数据正常则为0) )。我有以下计算,完美无缺;

'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).

并给出了我追求的结果。但是我很难找到正确的语法来创建游标,它将查看临时表中的每一行并相应地运行此SQL。然后,它需要使用查询结果(1或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

3 个解决方案

#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

  将数据类型更改为sysname,smallint以匹配表定义或名称可能会被截断

  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;
  

#2

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

如果我理解你的问题,我会做这样的事情来识别那些不作为[int]强制转换的记录。

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

您没有说明您正在使用的SQL Server版本; TRY_CAST和TRY_CONVERT是2012年或更晚。

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

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

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

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

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

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)

- 这是游标CURSOR的基本sql语法(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;

#3

I wasn't able to test this but it should do what you're looking for. Just plop this in after you create your temp table:

我无法测试这个,但它应该做你想要的。在创建临时表后,只需将其填入:

DECLARE @tName VARCHAR(20),
        @fName VARCHAR(20),
        @dType VARCHAR(20),
        @dLength INT,
        @sql VARCHAR(MAX);

DECLARE c CURSOR LOCAL FAST_FORWARD FOR
    SELECT Table_Name,
           Field_Name,
           Data_Type_Desc,
           Data_Max_Length
    FROM #BaseData;
OPEN c;
FETCH NEXT FROM c INTO @tName, @fName, @dType, @dLength;

WHILE @@FETCH_STATUS = 0
BEGIN
    IF((COUNT(@FieldName) - SUM(ISNUMERIC(@FieldName))) > 0)
    BEGIN
        SET @sql = 'UPDATE ' + @tName + ' SET Convertible = 1 WHERE Table_Name = ''' + @tName + '''';
    END
    ELSE
    BEGIN
        SET @sql = 'UPDATE ' + @tName + ' SET Convertible = 0 WHERE Table_Name = ''' + @tName + '''';
    END

    EXEC(@sql); 

    FETCH NEXT FROM c INTO @TableName, @FieldName, @DataType, @DataLength;
END

CLOSE c;
DEALLOCATE c;