自定义函数实现NULL值替换

数据库环境：SQL SERVER 2005

　　有时候，想将查询查询数来的数据格式化一下，不希望显示NULL值，或者复制表的数据时，被插入的表不允许有NULL。

我们可以通过ISNULL()函数或者COALESCE()函数对数据进行转换。

　　如果要转换的字段太多，不想一个个字段去转，是否有什么办法批量转换NULL值呢？

　　实现思路：

　　我们通过系统表sys.all_columns关联sys.types表找到某个表的所有字段及其类型，如果该字段是允许NULL的，则进行转换。

转换规则：如果是数值类型，则将NULL转换成0，否则，转换成空字符串。

　　下面是SQL实现，已经将它封装到表变量中，需要的时候直接传个表名进去调用即可。

ALTER FUNCTION [dbo].[f_null_cast]
    (
      @table_name VARCHAR(20)--表名
    )
RETURNS VARCHAR(MAX)
AS
    BEGIN
        DECLARE @sql_text VARCHAR(MAX);
            WITH    x0
                      AS ( SELECT   TOP 200 ','
                                    + CASE WHEN c.is_nullable = 0 THEN c.name
                                           WHEN t.name IN ( 'smallint', 'int',
                                                            'decimal' )
                                           THEN 'ISNULL(' + c.name + ',0) as '
                                                + c.name
                                           ELSE 'ISNULL(' + c.name
                                                + ','''') as ' + c.name
                                      END AS name_column
                           FROM     sys.all_columns AS c ,
                                    sys.types AS t
                           WHERE    c.system_type_id = t.system_type_id
                                    AND t.name <> 'sysname'
                                    AND c.object_id = OBJECT_ID(@table_name)
                                    ORDER BY c.column_id--根据字段创建的先后顺序排序
                         )
            SELECT  @sql_text = 'select ' + STUFF(( SELECT  name_column + ''
                                                    FROM    x0
                                                  FOR
                                                    XML PATH('')
                                                  ), 1, 1, '') + ' from '
                    + @table_name
        RETURN @sql_text

    END

View Code

　　有一张testdemo表，表结构的定义语句如图1：

　　执行SQL语句 “SELECT dbo.f_null_cast('testdemo') AS sql_text”，得到转换的结果如图2，图2的SQL是我用格式化工具

调整格式后的结果。

转载于:https://www.cnblogs.com/boss-he/p/4814137.html