Declare @i int,@enterpriseid bigint
Set @i = 2000
while @i < 5000
begin
Exec xx @i,@enterpriseid output
insert into Res_E (V_id,V_Eid) values ('05718000'+cast(@i as varchar(4)),@enterpriseid)
set @i = @i + 1 --加上set
end
Set @i = 2000
while @i < 5000
begin
Exec xx @i,@enterpriseid output
insert into Res_E (V_id,V_Eid) values ('05718000'+cast(@i as varchar(4)),@enterpriseid)
set @i = @i + 1 --加上set
end
Set @i = 2000
while @i < 5000
begin
Exec xx @i,@enterpriseid output --存储过程,输出@enterpriseid
set @sqlstr = N'insert into Res_E (V_id,V_Eid) values ('
+'05718000'+cast(@i as varchar)+','+@enterpriseid+')'
execute sp_executesql @sqlstr
set @i = @i + 1 --错误地方
end
begin
Exec xx @i,@enterpriseid outputset @sqlstr = 'insert into Res_E (V_id,V_Eid) values ('
+'05718000'+cast(@i as varchar(4))+','+@enterpriseid+')'
execute sp_executesql @sqlstr
set @i = @i + 1
end -------------------------------------将数据类型 varchar 转换为 bigint 时出错。数据类型不对吗??查看表结构是对应的呀
Exec xx @i,@enterpriseid output执行一次把表里的第@i条记录取出来(该记录只有一个enterpriseid)
@i int, ---输入参数
@enterpriseid bigint output ---输出参数
as
declare @Sqlstr nvarchar(500)set @Sqlstr = 'select top 1 @resid = Resid from
ResAppBasicInfo where Resid not in (select top '+ cast(@i-1 as varchar(4))
+ ' Resid from ResAppBasicInfo)'
execute sp_executesql @sqlstr,N'@resid bigint output',@enterpriseid output
begin
Exec xx @i,@enterpriseid outputset @sqlstr = 'insert into Res_E (V_id,V_Eid) values ('
+'05718000'+cast(@i as varchar(4))+',@enterpriseid)'
execute sp_executesql @sqlstr
set @i = @i + 1
end
try
________________________________________服务器: 消息 214,级别 16,状态 2,过程 sp_executesql,行 11
过程需要参数 '@statement' 为 'ntext/nchar/nvarchar' 类型。
----------------------------
改成@sqlstr nvarchar(800)可以执行语句,但报这样的问题:
服务器: 消息 137,级别 15,状态 2,行 1
必须声明变量 '@enterpriseid'。
服务器: 消息 137,级别 15,状态 2,行 1
必须声明变量 '@enterpriseid'。
服务器: 消息 137,级别 15,状态 2,行 1
必须声明变量 '@enterpriseid'。
服务器: 消息 137,级别 15,状态 2,行 1
必须声明变量 '@enterpriseid'。
服务器: 消息 137,级别 15,状态 2,行 1
必须声明变量 '@enterpriseid'。
........
yangvxin1(小杨) @enterpriseid那里写错了
但改写到外面又报错误
真晕~~~-----------------Declare @i int,@enterpriseid bigint,@sqlstr nvarchar(800)Set @i = 2000while @i < 5000
begin
Exec xx @i,@enterpriseid outputset @sqlstr = N'insert into Res_E (V_id,V_Eid) values ('
+'05718000'+cast(@i as varchar(4))+','+@enterpriseid+')'
execute sp_executesql @sqlstr
set @i = @i + 1
end服务器: 消息 8114,级别 16,状态 5,行 9
将数据类型 nvarchar 转换为 bigint 时出错。
Declare @i int,@enterpriseid bigint,@sqlstr varchar(800)Set @i = 2000while @i < 5000
begin
Exec xx @i,@enterpriseid output set @sqlstr = 'insert into Res_E (V_id,V_Eid) values ('+'''05718000'+convert(char(5),@i)+
''',convert(int,'+convert(char (10),@enterpriseid)+'))' exec(@sqlstr)
set @i = @i + 1
end
再试一下。
--------
nboys的写法(第一种) 是可行的,测试通过Declare @i int,@enterpriseid bigint
Set @i = 2000
while @i < 5000
begin
Exec xx @i,@enterpriseid output
insert into Res_E (V_id,V_Eid) values ('05718000'+cast(@i as varchar(4)),@enterpriseid)
set @i = @i + 1 --加上set
end但第2种写法为社么会报错呢,
大家帮帮忙啊,不甚感激
Declare @i int,@enterpriseid bigint,@sqlstr nvarchar(800)Set @i = 2000while @i < 5000
begin
Exec xx @i,@enterpriseid outputset @sqlstr = 'insert into Res_E (V_id,V_Eid) values ('+'''05718000'+cast(@i as varchar(4))+''',convert(int,'+convert(char (10),@enterpriseid)+'))' exec sp_executesql @sqlstr
set @i = @i + 1
end
但速度出奇得慢,我以为存储过程速度N得快,真让人失望??顺便问下:
insert into Res_E (V_id,V_Eid) values (...., @enterpriseidV_Eid 是int类型啊,怎么会类型冲突呢,刚才??
begin
Exec xx @i,@enterpriseid outputset @sqlstr = 'insert into Res_E (V_id,V_Eid) values ('+'05718000'+cast(@i as varchar(4))+
','+convert(varchar (10),@enterpriseid)+')' exec sp_executesql @sqlstr
set @i = @i + 1
end---------------------------------------
上面第2种写法通过
@sqlstr 是nVarchar字符串啊,当然不允许int,bigint类型数据在其语句里出现
问题出在这里我发觉我写的语句执行起来速度好慢啊,大家有何更好的建议吗??