EXEC sp_rename stop after several times in a while loop-CodePudding

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.

CodePudding user response：

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.

CodePudding user response：

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 !