主页 / dba / 问题 / 211920
Accepted
James
Asked: 2018-07-12 09:21:11 +0800 CST

从表中去除空值的 SP

  • 772

我想我很接近，但撇号让我很生气：

    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

它打印出来：

     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
    '.

问题：对于挑剔的最终用户，我如何动态删除空值并将它们替换为 ''？

sql-server

2 个回答

  1. Best Answer

    对于按照您提议的方式全权修改数据，我会三思而后行，除非您一次性将此作为数据纯度工作的一部分。

    我会先编写代码使用非动态 T-SQL 进行修改，然后将其转换为动态 T-SQL，最后将其添加到存储过程以便于重复使用。

    例如，我们将在 tempdb 中创建一个表，然后更新一个具有空值的列：

    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;

    接下来，我们将这段代码添加到使用动态 T-SQL 的框架中，这样我们就不需要为要更新的每一列编写单独的语句：

    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;

    它使用游标遍历给定表中列值为空的所有 varchar、nvarchar、char 和 nchar 列。

    接下来，我们将创建一个存储过程来运行代码。存储过程有一个参数，@debug它允许我们查看UPDATE语句，或者实际运行UPDATE语句。

    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

    像这样运行存储过程，它实际上不会修改数据，因为我们已将@debug参数设置为其默认值。：

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

    如果我们真的想修改数据，像这样运行存储过程：

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

  2. 哇，如果你用 sql import export wizard 来做，它会帮你处理。

    不过，我仍然会让这个 SP 工作。为了科学。

    • 0

相关问题