EXEC sp_rename останавливается после нескольких раз в цикле while

Question

Я пытаюсь переименовать имя столбца в первую строку моей таблицы. (Я знаю, это не имеет смысла «первая строка без порядка» в SQL, но я должен сделать это для своего теста). Итак, я не хочу динамически искать мои старые и новые имена столбцов для выполнения sp_rename в цикле while. Это ма-код:

      DECLARE @i INT;
SET @i = 0;
DECLARE @oldnom NVARCHAR(MAX);
DECLARE @newnom NVARCHAR(MAX)  
DECLARE @sSQL NVARCHAR(MAX);
DECLARE @ParmDefinition NVARCHAR(MAX);
DECLARE @tablename NVARCHAR(MAX) ;
SET @tablename = N'Produit_A';
WHILE @i < (SELECT MAX(rownum) FROM (SELECT ROW_NUMBER() OVER(ORDER BY ORDINAL_POSITION ASC) AS rownum,COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @tablename AND COLUMN_NAME like 'Prop%') truc)
BEGIN
    SET @oldnom = 'Prop_'+CAST(@i AS NVARCHAR(MAX))
    SET @oldnom = 'dbo.'+@tablename+'.['+@oldnom+']'
    SET @i = @i + 1
    SELECT @sSQL = N'SELECT TOP 1 @retvalOUT = COALESCE('+@oldnom+','''+@oldnom+''') FROM dbo.' + @tablename;
    SET @ParmDefinition = N'@retvalOUT NVARCHAR(MAX) OUTPUT';
    EXEC sp_executesql @sSQL, @ParmDefinition, @retvalOUT=@newnom OUTPUT;
    IF @newnom <> @oldnom
    BEGIN
        SELECT @oldnom, @newnom;
--      EXEC sp_rename @oldnom, @newnom;
    END
END

Мой запрос работает правильно, SELECT @oldnom, @newnom; вернуть все столбцы, которые нужно переименовать, и можно сделать мой sp_rename.

Однако, когда я раскомментирую EXEC sp_rename @oldnom, @newnom; и выполнить мою просьбу.

У меня столбцы хорошо переименовываются, но не все. Мой exec останавливается без ошибок после определенного числа.

Если я изменю начало времени, следующие столбцы также будут переименованы, поэтому проблема не связана с ошибкой в ​​определенном столбце.

Я не понимаю, почему выбор работает нормально, но exec sp_rename перестает работать через несколько раз...

Я думал об ограничении выполнения в одном запросе, но не нашел никакой информации об этом.

Я также подумал о сообщении об ошибке, возвращаемом sp_renamecommand (Внимание: изменение любой части имени объекта может нарушить работу скриптов и хранимых процедур.), но то же самое, никакой информации.

Answer 1

Вы не предоставили нам первую строку с именами столбцов, так что нам пришлось бы ослепнуть.

Но, основываясь на том факте, что это работает, когда вы не переименовываете, держу пари, проблема заключается в вашем запросе внутри:

@i < select max(rownum) from (.....)truc

Обратите внимание, что этот запрос оценивается после каждой итерации. Это хорошо работает, когда вы не переименовываете столбцы, потому что каждый раз возвращает одно и то же.

Но если вы ДЕЙСТВИТЕЛЬНО переименовываете столбцы, вы удаляете строки из этого запроса по следующим причинам:

AND COLUMN_NAME like 'Prop%'

Это приводит к тому, что ваш запрос «перескакивает» через каждый второй столбец. Пример:

Первая итерация, row_number=1, столбец переименован в [SomeColumn1Title].

Вторая итерация. Первая строка больше не [SomeColumn1Title], а [SomeColumn2Title]. Однако вы находитесь в @i=2, поэтому вместо этого будет переименован столбец 3. И так далее.

Этот код действительно нестабилен. Вместо этого просто используйте курсор, который оценит ваш запрос только один раз.

Answer 2

Проблема заключалась в условии моего цикла while. Я исправлю свой код и буду использовать курсор, чтобы избежать цикла. Большое спасибо ! Вот мой исправленный код:

      DECLARE @tablename NVARCHAR(MAX);
DECLARE 
    @oldnom NVARCHAR(MAX), 
    @newnom NVARCHAR(MAX),
    @sSQL NVARCHAR(MAX),
    @ParmDefinition NVARCHAR(MAX),
    @requete NVARCHAR(MAX);
SET @tablename = N'Produit_A';
SET @requete = N'';
DECLARE cursor_col CURSOR FOR 
    SELECT name 
    FROM sys.columns 
    WHERE object_id = OBJECT_ID('bdd_canon.['+@tablename+']');
OPEN cursor_col;
FETCH NEXT FROM cursor_col INTO @oldnom ;
WHILE @@FETCH_STATUS = 0
    BEGIN
        SELECT @sSQL = N'SELECT TOP 1 @retvalOUT = COALESCE('+@oldnom+','''+@oldnom+''') FROM bdd_canon.' + @tablename;
        SET @ParmDefinition = N'@retvalOUT NVARCHAR(MAX) OUTPUT';
        EXEC sp_executesql @sSQL, @ParmDefinition, @retvalOUT=@newnom OUTPUT;
        IF @newnom <> @oldnom
        BEGIN
            SET @oldnom = '''bdd_canon.'+@tablename+'.['+@oldnom+']'''
            SET @newnom = ''''+@newnom+''''
            SET @requete = @requete + 'EXEC sp_rename '+@oldnom+', '+@newnom+',''COLUMN''; ';
        END
        FETCH NEXT FROM cursor_col INTO @oldnom ;
    END;
EXEC sp_executesql @requete
CLOSE cursor_col;
DEALLOCATE cursor_col;