EXEC sp_rename stop after several times in a while loop

Question

I try to rename my column name with the firt row of my table. (I know, it do not make sense 'a first row without order by' in SQL, but I have to do this for my test). So, I wan't to search dynamically my old and my new column names to execute sp_rename in a while loop. This is ma code :

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

My request work correctly, the SELECT @oldnom, @newnom; return all the columns that need to be renamed, and it's possible to make my sp_rename.

However, when I uncomment EXEC sp_rename @oldnom, @newnom; and execute my request.

My columns are well renamed, but not all of them. My exec stops without error after a certain number.

If I change the start of the while, the following columns are well renamed too, so the problem is not due to an error on a certain column.

I don't understand why the select work fine but the exec sp_rename stop working after a few times ...

I thought about a limit of executions in a single request but I couldn't find any information about it.

I also thought about the error message returned by the sp_rename command (Caution: Changing any part of an object name could break scripts and stored procedures.), but same, no information.

You have `SELECT TOP 1` without an `ORDER BY`... do you expect to get the same row each time, a sequential "next" row, an arbitrary/random row, or something else? Can you build an actual full example (including original table structure, sample data you're using to rename columns, and what you expect the table structure to look like after all the renames) on [db<>fiddle](https://sqlblog.org/fiddle)? It's very hard for us to troubleshoot what might be going on in your dynamic SQL if we can't build it ourselves. — Aaron Bertrand, Mar 14 '22 at 17:03
Tip: The best practice when assembling object names into dynamic SQL statements is to use [`QuoteName()`](https://learn.microsoft.com/en-us/sql/t-sql/functions/quotename-transact-sql) to avoid problems with odd names, e.g. `New Table` with a space or reserved words like `From`. — HABO, Mar 14 '22 at 17:53
The origin of my problem is that's impossible to import an excel sheet with first row as header if this first row contains empty values (import with ADF). So I avoid the problem by import the excel file without check the "first row as header", so the table have Prop_0, Prop_1 .... as column names, and the first row is the row where good column names are (but some of them are NULL). I'll post an example of my table in comment. I know use TOP 1 without ORDER BY doesn't make sense but I don't have other solution for now. Thank's for the tip, I'll use it ! — Michiyo, Mar 14 '22 at 18:47

score 1 · Accepted Answer · answered Mar 14 '22 at 18:59

You haven't provided us the first row with the column names, so we'd have to go blind.

But based on the fact it works when you don't rename, I bet the problem is your query inside the while:

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

Notice that this query is evaluated after every iteration. It works well when you don't rename the columns, because each time it returns the same thing.

But if you DO rename the columns, you are removing rows from this query because of:

AND COLUMN_NAME like 'Prop%'

This causes your query to "jump" every other column. Example:

First iteration, row_number=1, column renamed to [SomeColumn1Title].

Second iteration. First row is not [SomeColumn1Title] anymore, but [SomeColumn2Title]. However, you are at @i=2, so what will happend is that column 3 will renamed instead. And so on.

This code is really unstable. Just use a cursor instead, which will only evaluate your query once.

I never used cursor before, I'll look, thank's again =) – Michiyo Mar 14 '22 at 19:12 — Michiyo, Mar 14 '22 at 19:12
Agree on the cursor, that was my first thought as well – Xavier J Mar 14 '22 at 20:52 — Xavier J, Mar 14 '22 at 20:52

Michiyo · Answer 2 · 2022-03-15T08:49:21.277

The problem was in the condition for my while loop. I'll fix my code and use cursor to avoid the loop. Thank's a lot ! Here is my corrected code :

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;

EXEC sp_rename stop after several times in a while loop

2 Answers2