isnull動態處理所有的動態列 –【葉子】

isnull動態處理所有的動態列

-- =================================

-- Author:<Maco Wang>

-- CreateDate:2009-04-03 13:15:35

-- Description:<處理一個表的所有為空白列，列是動態添加後的不固定列>

-- =================================

create procedure getnotnull

(

  @tablename varchar(100), --表名

  @Na varchar(20)          --替換null的字元

)

as

begin

    declare @sql varchar(8000)

    set @sql=''

    select @sql=@sql+',isnull(cast('+name+' as varchar),'+@Na+') as '+name

    from syscolumns where id=object_id(@tablename) order by colid

    set @sql=stuff(@sql,1,1,'')

    --print @sql

    exec('select '+@sql+' from '+@tablename) 

end

 

--測試資料

go

create table testtb(id int ,nn varchar(20))

insert into testtb

select null,null union all

select null,'a' union all

select 2,null

 

--處理非空列

go

exec getnotnull 'testtb','0'

go

drop table testtb

 

/*運行結果

id                             nn

------------------------------ ------------------------------

0                              0

0                              a

2                              0

*/