继续问
代码是一位大侠提供的，老是提示
服务器: 消息 137，级别 15，状态 2，行 54
必须声明变量 '@ '。
go
if object_id( 'SHSFXQXXHWYHCARD..C ') is not null
drop table C
go
create table C(a5 varchar(20),b5 varchar(20))
insert into C(a5,b5) values( 'ZWW-XJG ', '1-040901 ')
insert into C(a5,b5) values( 'ZWW-JF ', '1-040901 ')
insert into C(a5,b5) values( 'ZWW-NTPC ', '1-040901 ')
insert into C(a5,b5) values( 'ZWW-PC ', '1-040901 ')
insert into C(a5,b5) values( 'ZSW04 ', '1-050401 ')
insert into C(a5,b5) values( 'ZSW05 ', '1-050401 ')
insert into C(a5,b5) values( 'ZWW-YBY ', '1-050602 ')
insert into C(a5,b5) values( 'ZWW-JCYYW ', '1-050603 ')
insert into C(a5,b5) values( 'GWW-JCYYW ', '2-050603 ')
insert into C(a5,b5) values( 'ZWW-GDW ', '1-050704 ')
insert into C(a5,b5) values( 'GWW-GDW ', '2-050704 ')
insert into C(a5,b5) values( 'ZWW-VOD ', '1-050805 ')
insert into C(a5,b5) values( 'ZWW-RMFY ', '1-050906 ')
insert into C(a5,b5) values( 'MHWZ ', '3-051007 ')
insert into C(a5,b5) values( 'ZWW-DZYZ-001 ', '1-051008 ')
insert into C(a5,b5) values( 'ZWW-SWJIT ', '1-060401 ')
insert into C(a5,b5) values( 'ZWW-YJZH ', '1-060402 ')
insert into C(a5,b5) values( 'ZWW-JYC ', '1-060403 ')
insert into C(a5,b5) values( 'ZWW-GIS ', '1-060504 ')
insert into C(a5,b5) values( 'ZWW-CXW ', '1-060505 ')
insert into C(a5,b5) values( 'ZWW-JSWFW ', '1-060506 ')
insert into C(a5,b5) values( 'ZWW-JG ', '1-060709 ')
insert into C(a5,b5) values( 'ZWW-RDZXTA ', '1-060811 ')
insert into C(a5,b5) values( 'ZZBXXGLXT ', '1-060812 ')
insert into C(a5,b5) values( 'ZWW-JCYDA ', '1-060813 ')
insert into C(a5,b5) values( 'ZWW-WZBWZ ', '1-061116 ')
insert into C(a5,b5) values( 'ZWW-YJSJ ', '1-061121 ')
insert into C(a5,b5) values( 'ZWW-DZYZ-003 ', '1-061122 ')
insert into C(a5,b5) values( 'ZWW-FBDRJ ', '1-061223 ')
insert into C(a5,b5) values( 'ZWW-ZBRJ ', '1-060324 ')
insert into C(a5,b5) values( 'ZWW-SFJ-002 ', '1-061025 ')
go
if object_id( 'SHSFXQXXHWYHCARD..D ') is not null
drop table D
go
select id = identity(int,1,1) , * into D from FA_Kp
declare @count as int
select @count = count(*) from D
declare @i as int
set @i = 1
declare @s2 as varchar(20)
declare @s3 as varchar(20)
declare @s4 as varchar(50)
declare @s5 as varchar(30)
declare @s6 as varchar(40)
declare @s7 as numeric(9)
declare @s8 as varchar(60)
declare @s9 as varchar(40)
declare @a as varchar(20)
declare @b as numeric(9)
declare @c as numeric(20)
declare @d as varchar(200)
while @i <= @count
begin
set @s2 = null
set @s3 = null
set @s4 = null
set @s5 = null
set @s6 = null
set @s7 = null
set @s8 = null
set @s9 = null
set @a = null
set @b = null
set @c = null
set @d = null
if @i <=83 set @s2= '1-040901 '
else
begin
select @s2 = C.b5 from D,C where D.id = @i and D.a5 = C.a5
end
select @s3 = max(serialcode) from test070409 where left(serialcode,8) = @s2
if @s3 is null or len(rtrim(@s3)) = 0 set @s3 = '001 '
else
begin
set @s3 = right( '000 ' + cast(cast(right(@s3,3) as int) + 1 as varchar),3)
end
select @s4 = gdmc from D where id = @i
select @s5 = lbmc from D where id = @i
select @s6 = gdgg from D where id = @i
select @s7 = ksrq from D where id = @i
set @s7=cast(@s7 as char)
select @s8 = bmmc from D where id = @i
select @s9 = sybm from D where id = @i
select @a = zkmc from D where id = @i
select @b = yz from D where id = @i
select @c = jz from D where id = @i
select @d = gd1 from D where id = @i
insert into test070409(serialcode,assetsname,assetssortcode,assetsstandard,assetsusedate,assetslayadd,assetsdept,usestate,assetsprice,leftprice,remarks) values(@s2 + '- ' + @s3,@s4,@s5,@s6,@s7,@s8,@,s9,@a,@b,@c,@d)
set @i = @i + 1
end
select * from test070409
drop table FA_Kp,test070409,C,D
[解决办法]
@s3,@s4,@s5,@s6,@s7,@s8,@,s9,@a,@b,@c,@d)
set @i = @i + 1
end
select * from test070409
drop table FA_Kp,test070409,C,D
[解决办法]
select id = identity(int,1,1) , * into D from FA_Kp
declare @count as int
select @count = count(*) from D
declare @i as int
set @i = 1
declare @s2 as varchar(20)
declare @s3 as varchar(20)
declare @s4 as varchar(50)
declare @s5 as varchar(30)
declare @s6 as varchar(40)
declare @s7 as numeric(9)
declare @s8 as varchar(60)
declare @s9 as varchar(40)
declare @a as varchar(20)
declare @b as numeric(9)
declare @c as numeric(20)
declare @d as varchar(200)
while @i <= @count
begin
set @s2 = null
set @s3 = null
set @s4 = null
set @s5 = null
set @s6 = null
set @s7 = null
set @s8 = null
set @s9 = null
set @a = null
set @b = null
set @c = null
set @d = null
if @i <=83 set @s2= '1-040901 '
else
begin
select @s2 = C.b5 from D,C where D.id = @i and D.a5 = C.a5
end
select @s3 = max(serialcode) from test070409 where left(serialcode,8) = @s2
if @s3 is null or len(rtrim(@s3)) = 0 set @s3 = '001 '
else
begin
set @s3 = right( '000 ' + cast(cast(right(@s3,3) as int) + 1 as varchar),3)
end
select @s4 = gdmc from D where id = @i
select @s5 = lbmc from D where id = @i
select @s6 = gdgg from D where id = @i
select @s7 = ksrq from D where id = @i
set @s7=cast(@s7 as char)
select @s8 = bmmc from D where id = @i
select @s9 = sybm from D where id = @i
select @a = zkmc from D where id = @i
select @b = yz from D where id = @i
select @c = jz from D where id = @i
select @d = gd1 from D where id = @i
insert into test070409(serialcode,assetsname,assetssortcode,assetsstandard,assetsusedate,assetslayadd,assetsdept,usestate,assetsprice,leftprice,remarks) values(@s2 + '- ' +@s3,@s4,@s5,@s6,@s7,@s8,@s9,@a,@b,@c,@d)
set @i = @i + 1
end
select * from test070409
[解决办法]
insert into test070409(serialcode,assetsname,assetssortcode,assetsstandard,assetsusedate,assetslayadd,assetsdept,usestate,assetsprice,leftprice,remarks)
values(@s2 + '- ' + @s3,@s4,@s5,@s6,@s7,@s8,@,s9 --这里，多写了一个,
,@a,@b,@c,@d)
set @i = @i + 1
end
