Where Case

--第一次看到这样写的，where 里面case,觉得很厉害

create table A(id int,a varchar(6),b varchar(6),c varchar(6),d varchar(6),e varchar(6))
insert into A select 1,'面包','牛奶','尿布','啤酒',NULL 
insert into A select 2,'可乐','牛奶','尿布',NULL  ,NULL 
insert into A select 3,'面包','可乐','牛奶',NULL  ,NULL 
insert into A select 4,'可乐','啤酒',NULL  ,NULL  ,NULL 
insert into A select 5,'可乐','牛奶','尿布','花生',NULL 
insert into A select 6,'可乐','牛奶','花生',NULL  ,NULL 
insert into A select 7,'面包','可乐','牛奶','尿布','啤酒'
insert into A select 8,'面包','牛奶','尿布','花生','啤酒'
insert into A select 9,'面包',NULL  ,NULL  ,NULL  ,NULL 
go

create procedure shiyan(@tname varchar(10),@num int)
as
begin
    declare @sql varchar(8000)
    set @sql=''
   
    select
        @sql=@sql+'+(case when '+name+' is null then 0 else 1 end)'
    from
        syscolumns
    where 
        id=object_id(@tname) and name!='id'
   
    set @sql='delete '+@tname+' where '+stuff(@sql,1,1,'')+'='+rtrim(@num)
   
    exec(@sql)
end
go