title: Very slow stored procedure with cursor
body: <p>I wrote a simple stored procedure to replace empty strings with null in each column of a given table if the data type is varchar or nvarchar. </p>
  
  <p>The procedure works fine for the most part but when it's run over a large table (15 columns with 10,000,000 rows) it can take hours.</p>
  
  <p>I have tested running the updates that would run in the procedure individually and they all take less than a minute on the large tables. This has me thinking there must be something wrong with the procedure. I am new to sql server, is there something wrong here?</p>
  
  <pre><code>ALTER PROCEDURE [dbo].[PROC_CLEAN_BAD_DATA]
  
  (
        @table_name nvarchar(100)
  )
  
  AS
        SET NOCOUNT ON
  BEGIN
  
        DECLARE @column nvarchar(100);
  
        DECLARE columns CURSOR
        FOR SELECT COLUMN_NAME
               FROM INFORMATION_SCHEMA.COLUMNS
               WHERE TABLE_NAME = @table_name
               and data_type IN ('VARCHAR', 'NVARCHAR');
  
        OPEN columns;
  
        FETCH NEXT FROM columns into @column;
  
        WHILE @@FETCH_STATUS = 0
  
               BEGIN                 
  
                      declare @bad_data_count int = 0,
                      @select_sql nvarchar(max) = 'SELECT @bad_data_count = COUNT(*) FROM [dbo].[' + @table_name + '] WHERE dbo.trim([' + @column + ']) = '''';';                   
  
                      BEGIN
  
                            EXEC sp_executesql @select_sql, N'@bad_data_count int OUTPUT', @bad_data_count OUTPUT;
  
                            if @bad_data_count != 0
  
                                   BEGIN
  
                                         DECLARE @update_sql nvarchar(max) = 'UPDATE [dbo].[' + @table_name + '] set [' + @column + '] = null WHERE dbo.trim([' + @column + ']) = '''';'
                                         print @update_sql
                                         EXEC sp_executesql @update_sql;
  
                                   END
  
                      END
  
                      FETCH NEXT FROM columns into @column;
  
               END
  
        CLOSE columns; DEALLOCATE columns;
  
  END
  </code></pre>