reate proc src_cc
(@bus_name varchar(20))
as
begin
select * into #t1 from Chengdu_bus where bus_name like @bus_name+'%'
alter table #t1 add stops varchar(500) default '暂无'
select * from #t1
end
GO
上面这个存储过程中的临时表#t1新增的名为stops的列当我把其初值设为null时想通过select stops from #t1返回此列的空值但是系统提示:"stops列无效",我想用default把这个新增列的值都设为'暂无'(如上代码所示),结果用select * from #t返回所有值发现stops字段还是为null,单取stops字段值仍旧提示此列无效,我只想在这里设定一个默认值,然后后面用update...set来依次更新此字段的值。请教解决办法。
(@bus_name varchar(20))
as
begin
select * into #t1 from Chengdu_bus where bus_name like @bus_name+'%'
alter table #t1 add stops varchar(500) default '暂无'
select * from #t1
end
GO
上面这个存储过程中的临时表#t1新增的名为stops的列当我把其初值设为null时想通过select stops from #t1返回此列的空值但是系统提示:"stops列无效",我想用default把这个新增列的值都设为'暂无'(如上代码所示),结果用select * from #t返回所有值发现stops字段还是为null,单取stops字段值仍旧提示此列无效,我只想在这里设定一个默认值,然后后面用update...set来依次更新此字段的值。请教解决办法。
(@bus_name varchar(20))
as
begin
select * into #t1 from Chengdu_bus where bus_name like @bus_name+'%'
alter table #t1 add stops varchar(500) default null
update #t1 set stops = '暂无' where stops is null
select * from #t1
end
GO
==》
exec('select * from #t1')
as
begin
select * into #t1 from Chengdu_bus where 1=0
alter table #t1 add stops varchar(500) default '暂无'
insert into #t1(Chengdu_b表的所有字段)
select * from Chengdu_bus where bus_name like @bus_name+'%'end
GO
utpcb建空表再插值,太麻烦了,还要为后面从#t1取值方便考虑哈
yrwx001的方法不错