@tmpTB1 是 exec(…) 外声明的变量,所以在运行时提示必须声明变量 所以你还是改用别的办法来实现将 set @sql='insert into @tmpTB1 select bm_id,bjsj,substring(zfzj,2,100) as zfzj from ZFGZ_GZ_B1 where 1=1' exec (@sql) 改为 insert into @tmpTB1 select bm_id,bjsj,substring(zfzj,2,100) as zfzj from ZFGZ_GZ_B1 where 1=1
@bmid int,@bjsj1 varchar(10),@bjsj2 varchar(10)
AS
begin
declare @i int,
@k int,
@bm_id int,
@ajs int,
@ajs2 int,
@sql varchar(8000)TRUNCATE TABLE ZFGZ_RP1
set @sql='
DECLARE @tmpTB1 TABLE
(
bm_id int,
bjsj varchar(10),
zfzj varchar(100)
)DECLARE @bmTB1 TABLE
(
bm_id int,
ajs int,
FlagID TINYINT
)
insert into @tmpTB1 select bm_id,bjsj,substring(zfzj,2,100) as zfzj from ZFGZ_GZ_B1 where 1=1' --这一段如何写?
exec (@sql)END
表变量带不进去,换表可以,第二种就是把表变量写到exec里,但好象没什么意义....
可以这么写:
set @sql='insert into '+@tmpTB1+' select bm_id,bjsj,substring(zfzj,2,100) as zfzj from ZFGZ_GZ_B1 where 1=1'
为方便大家测试我把它改成以下代码,如果可以调用成功,那么就成了。CREATE PROCEDURE sp_pp1 AS
DECLARE @sql varchar(200)
DECLARE @tmpTB1 TABLE
(
bm_id int,
bjsj varchar(10),
zfzj varchar(100)
) BEGIN set @sql='delete from @tmpTB1 '
exec (@sql) ENDGO
所以你还是改用别的办法来实现将
set @sql='insert into @tmpTB1 select bm_id,bjsj,substring(zfzj,2,100) as zfzj from ZFGZ_GZ_B1 where 1=1'
exec (@sql) 改为
insert into @tmpTB1 select bm_id,bjsj,substring(zfzj,2,100) as zfzj from ZFGZ_GZ_B1 where 1=1