Início / dba / Perguntas / 211920
Accepted
James
Asked: 2018-07-12 09:21:11 +0800 CST

um SP para remover nulos de uma tabela

  • 772

Acho que estou perto, mas os apóstrofos estão me matando:

    create procedure denull (@database varchar(255), @table varchar(255))

    as

    declare @sql nvarchar(max)

    set @sql = ' declare @name varchar(255), @column varchar(255))

    declare c cursor local for 

    select st.name, sc.name from ' + @database + '.sys.tables st
    inner join sys.columns sc on st.object_id = sc.object_id
    where st.name = ''' + @table + '''

    update ''''@name'''' set ''''@column'''' = '''' where ''''@column is null''



    fetch next from st.name, sc.name

    close c

    deallocate c
    '
    print @sql

    exec sp_executesql @sql

ele imprime para:

     declare @name varchar(255), @column varchar(255))

    declare c cursor local for 

    select st.name, sc.name from livendbmirror.sys.tables st
    inner join sys.columns sc on st.object_id = sc.object_id
    where st.name = 'CMS_27_Psych_results'

    update ''@name'' set ''@column'' = '' where ''@column is null'



    fetch next from st.name, sc.name

    close c

    deallocate c
    Msg 102, Level 15, State 1, Line 1
    Incorrect syntax near ')'.
    Msg 102, Level 15, State 1, Line 9
    Incorrect syntax near ''.
    Msg 105, Level 15, State 1, Line 9
    Unclosed quotation mark after the character string '



    fetch next from st.name, sc.name

    close c

    deallocate c
    '.

Pergunta: Como posso remover dinamicamente valores nulos e substituí-los por '' para usuários finais exigentes?

sql-server

2 respostas

  1. Best Answer

    Eu pensaria duas vezes antes de modificar os dados com carta branca da maneira que você está propondo, a menos que você esteja fazendo isso uma vez como parte de um esforço de pureza de dados.

    Eu escreveria o código para fazer a modificação usando primeiro o T-SQL não dinâmico, depois o converteria em T-SQL dinâmico e, finalmente, o adicionaria a um procedimento armazenado para fácil reutilização.

    Por exemplo, criaremos uma tabela em tempdb e atualizaremos uma coluna que tenha valores nulos:

    USE tempdb;

DROP TABLE dbo.test_table;
CREATE TABLE dbo.test_table (i varchar(3) null);
INSERT INTO dbo.test_table(i) VALUES (null)

UPDATE [dbo].[test_table]
SET [i] = '' 
WHERE [i] IS NULL;

    Em seguida, adicionaremos este código a uma estrutura que usa T-SQL dinâmico para que não precisemos escrever instruções individuais para cada coluna que desejamos atualizar:

    DECLARE @cmd nvarchar(max);
DECLARE @schema sysname;
DECLARE @table sysname;
DECLARE @column sysname;
DECLARE cur CURSOR LOCAL FORWARD_ONLY STATIC READ_ONLY
FOR
SELECT s.name
    , t.name
    , c.name
FROM sys.schemas s
    INNER JOIN sys.tables t ON s.schema_id = t.schema_id
    INNER JOIN sys.columns c ON t.object_id = c.object_id
    INNER JOIN sys.types ty ON c.system_type_id = ty.system_type_id
WHERE c.is_nullable = 1
    AND ty.name IN (
          'varchar'
        , 'char'
        , 'nvarchar'
        , 'nchar'
    )
    AND s.name = 'dbo'
    AND t.name = 'test_table'
ORDER BY s.name
    , t.name
    , c.name;

OPEN cur;
FETCH NEXT FROM cur INTO @schema, @table, @column;
WHILE @@FETCH_STATUS = 0
BEGIN
    SET @cmd = 'UPDATE ' + QUOTENAME(@schema) + '.' + QUOTENAME(@table) + '
SET ' + QUOTENAME(@column) + ' = '''' 
WHERE ' + QUOTENAME(@column) + ' IS NULL;';
    PRINT @cmd;
    FETCH NEXT FROM cur INTO @schema, @table, @column;
END
CLOSE cur;
DEALLOCATE cur;

    Ele usa um cursor para iterar sobre todas as colunas varchar, nvarchar, char e nchar na tabela em que o valor da coluna é nulo.

    Em seguida, criaremos um procedimento armazenado para executar o código. O proc armazenado tem um parâmetro, @debug, que nos permite ver a UPDATEinstrução ou realmente executar a UPDATEinstrução.

    IF OBJECT_ID(N'dbo.RemoveNulls', N'P') IS NOT NULL
DROP PROCEDURE dbo.RemoveNulls;
GO
CREATE PROCEDURE dbo.RemoveNulls
(
    @schema_to_change sysname
    , @table_to_change sysname
    , @debug bit = 1
)
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @cmd nvarchar(max);
    DECLARE @schema sysname;
    DECLARE @table sysname;
    DECLARE @column sysname;
    DECLARE cur CURSOR LOCAL FORWARD_ONLY STATIC READ_ONLY
    FOR
    SELECT s.name
        , t.name
        , c.name
    FROM sys.schemas s
        INNER JOIN sys.tables t ON s.schema_id = t.schema_id
        INNER JOIN sys.columns c ON t.object_id = c.object_id
        INNER JOIN sys.types ty ON c.system_type_id = ty.system_type_id
    WHERE c.is_nullable = 1
        AND ty.name IN (
              'varchar'
            , 'char'
            , 'nvarchar'
            , 'nchar'
        )
        AND s.name = @schema_to_change
        AND t.name = @table_to_change
    ORDER BY s.name
        , t.name
        , c.name;

    OPEN cur;
    FETCH NEXT FROM cur INTO @schema, @table, @column;
    WHILE @@FETCH_STATUS = 0
    BEGIN
        SET @cmd = 'UPDATE ' + QUOTENAME(@schema) + '.' + QUOTENAME(@table) + '
SET ' + QUOTENAME(@column) + ' = '''' 
WHERE ' + QUOTENAME(@column) + ' IS NULL;';
        PRINT @cmd;
        IF @debug = 0
        BEGIN
            EXEC sys.sp_executesql @cmd;
        END
        FETCH NEXT FROM cur INTO @schema, @table, @column;
    END
    CLOSE cur;
    DEALLOCATE cur;
END
GO

    Execute o procedimento armazenado como este, que na verdade não modificará os dados, pois deixamos o @debugparâmetro definido com seu valor padrão.:

    EXEC dbo.RemoveNulls @schema_to_change = 'dbo'
    , @table_to_change = 'test_table';

    Se quisermos realmente modificar os dados, execute o procedimento armazenado assim:

    EXEC dbo.RemoveNulls @schema_to_change = 'dbo'
    , @table_to_change = 'test_table'
    , @debug = 0;
    • 4

  2. Uau, se você fizer isso com o assistente de exportação de importação sql, ele apenas lida com isso para você.

    Eu ainda vou fazer este SP funcionar, no entanto. Pela ciência.

    • 0

relate perguntas