CREATE PROCEDURE [insert_CFMap_Points_2]
(@ID_1 [varchar](8000),
@FeatureID_2 [varchar](8000),
@X_3 [varchar](8000),
@Y_4 [varchar](8000),
@parts_5 [varchar](1000))AS
DECLARE @PointerPrev1 int
DECLARE @PointerCurr1 int
DECLARE @PointerPrev2 int
DECLARE @PointerCurr2 int
DECLARE @PointerPrev3 int
DECLARE @PointerCurr3 int
DECLARE @PointerPrev4 int
DECLARE @PointerCurr4 int
DECLARE @PointerPrev5 int
DECLARE @PointerCurr5 int
DECLARE @TID_1 varchar(50)
DECLARE @TFeatureID_2 varchar(50)
DECLARE @TX_3 float
DECLARE @TY_4 float
DECLARE @Tparts_5 int Set @PointerPrev1=1
Set @PointerCurr1=1
Set @PointerPrev2=1
Set @PointerCurr2=1
Set @PointerPrev3=1
Set @PointerCurr3=1
Set @PointerPrev4=1
Set @PointerCurr4=1
Set @PointerPrev5=1
Set @PointerCurr5=1 begin transaction
Set NoCount ON Set @PointerCurr1=CharIndex('|',@ID_1,@PointerPrev1+1)
Set @TID_1 =cast(SUBSTRING(@ID_1,@PointerPrev1,@PointerCurr1-@PointerPrev1) as varchar)
Set @PointerCurr2=CharIndex('|',@FeatureID_2,@PointerPrev2+1)
Set @TFeatureID_2 =cast(SUBSTRING(@FeatureID_2,@PointerPrev2,@PointerCurr2-@PointerPrev2) as varchar)
Set @PointerCurr3=CharIndex('|',@X_3,@PointerPrev3+1)
Set @TX_3 =cast(SUBSTRING(@X_3,@PointerPrev3,@PointerCurr3-@PointerPrev3) as float)
Set @PointerCurr4=CharIndex('|',@Y_4,@PointerPrev4+1)
Set @TY_4 =cast(SUBSTRING(@Y_4,@PointerPrev4,@PointerCurr4-@PointerPrev4) as float)
Set @PointerCurr5=CharIndex('|',@parts_5,@PointerPrev5+1)
Set @Tparts_5 =cast(SUBSTRING(@parts_5,@PointerPrev5,@PointerCurr5-@PointerPrev5) as int)
Insert into CFMap_Points ([ID],[FeatureID],[X],[Y], [parts]) Values(@TID_1,@TFeatureID_2,@TX_3,@TY_4, @Tparts_5)
SET @PointerPrev1= @PointerCurr1
SET @PointerPrev2= @PointerCurr2
SET @PointerPrev3= @PointerCurr3
SET @PointerPrev4= @PointerCurr4
SET @PointerPrev5= @PointerCurr5
while ( (@PointerPrev1+1 < LEN(@ID_1)) and (@PointerPrev2+1 < LEN(@FeatureID_2)) and (@PointerPrev3+1 < LEN(@X_3)) and (@PointerPrev4+1 < LEN(@Y_4)) and (@PointerPrev5+1 < LEN(@parts_5)) )
Begin
Set @PointerCurr1=CharIndex('|',@ID_1,@PointerPrev1+1)
Set @PointerCurr2=CharIndex('|',@FeatureID_2,@PointerPrev2+1)
Set @PointerCurr3=CharIndex('|',@X_3,@PointerPrev3+1)
Set @PointerCurr4=CharIndex('|',@Y_4,@PointerPrev4+1)
Set @PointerCurr5=CharIndex('|',@parts_5,@PointerPrev5+1)
if(@PointerCurr1>0 and @PointerCurr2>0 and @PointerCurr3>0 and @PointerCurr4>0 and @PointerCurr5>0)
Begin
Set @TID_1 =cast(SUBSTRING(@ID_1,@PointerPrev1+1,@PointerCurr1-@PointerPrev1-1) as varchar)
Set @TFeatureID_2 =cast(SUBSTRING(@FeatureID_2,@PointerPrev2+1,@PointerCurr2-@PointerPrev2-1) as varchar)
Set @TX_3 =cast(SUBSTRING(@X_3,@PointerPrev3+1,@PointerCurr3-@PointerPrev3-1) as float)
Set @TY_4 =cast(SUBSTRING(@Y_4,@PointerPrev4+1,@PointerCurr4-@PointerPrev4-1) as float)
Set @Tparts_5 =cast(SUBSTRING(@parts_5,@PointerPrev5+1,@PointerCurr5-@PointerPrev5-1) as int)
Insert into [mapx].[dbo].[CFMap_Points] ([ID],[FeatureID],[X],[Y], [parts]) Values(@TID_1,@TFeatureID_2,@TX_3,@TY_4, @Tparts_5)
SET @PointerPrev1 = @PointerCurr1
SET @PointerPrev2 = @PointerCurr2
SET @PointerPrev3 = @PointerCurr3
SET @PointerPrev4 = @PointerCurr4
SET @PointerPrev5 = @PointerCurr5
End
else
Break
End
Set @TID_1 =cast(SUBSTRING(@ID_1,@PointerPrev1+1,LEN(@ID_1)-@PointerPrev1) as varchar)
Set @TFeatureID_2 =cast(SUBSTRING(@FeatureID_2,@PointerPrev2+1,LEN(@FeatureID_2)-@PointerPrev2) as varchar)
Set @TX_3 =cast(SUBSTRING(@X_3,@PointerPrev3+1,LEN(@X_3)-@PointerPrev3) as float)
Set @TY_4 =cast(SUBSTRING(@Y_4,@PointerPrev4+1,LEN(@Y_4)-@PointerPrev4) as float)
Set @Tparts_5 =cast(SUBSTRING(@parts_5,@PointerPrev5+1,LEN(@parts_5)-@PointerPrev5) as int)
Insert into [mapx].[dbo].[CFMap_Points] ([ID],[FeatureID],[X],[Y], [parts]) Values(@TID_1,@TFeatureID_2,@TX_3,@TY_4, @Tparts_5) Set NoCount OFF
if @@error=0
begin
commit transaction
end
else
begin
rollback transaction
end
GO
(@ID_1 [varchar](8000),
@FeatureID_2 [varchar](8000),
@X_3 [varchar](8000),
@Y_4 [varchar](8000),
@parts_5 [varchar](1000))AS
DECLARE @PointerPrev1 int
DECLARE @PointerCurr1 int
DECLARE @PointerPrev2 int
DECLARE @PointerCurr2 int
DECLARE @PointerPrev3 int
DECLARE @PointerCurr3 int
DECLARE @PointerPrev4 int
DECLARE @PointerCurr4 int
DECLARE @PointerPrev5 int
DECLARE @PointerCurr5 int
DECLARE @TID_1 varchar(50)
DECLARE @TFeatureID_2 varchar(50)
DECLARE @TX_3 float
DECLARE @TY_4 float
DECLARE @Tparts_5 int Set @PointerPrev1=1
Set @PointerCurr1=1
Set @PointerPrev2=1
Set @PointerCurr2=1
Set @PointerPrev3=1
Set @PointerCurr3=1
Set @PointerPrev4=1
Set @PointerCurr4=1
Set @PointerPrev5=1
Set @PointerCurr5=1 begin transaction
Set NoCount ON Set @PointerCurr1=CharIndex('|',@ID_1,@PointerPrev1+1)
Set @TID_1 =cast(SUBSTRING(@ID_1,@PointerPrev1,@PointerCurr1-@PointerPrev1) as varchar)
Set @PointerCurr2=CharIndex('|',@FeatureID_2,@PointerPrev2+1)
Set @TFeatureID_2 =cast(SUBSTRING(@FeatureID_2,@PointerPrev2,@PointerCurr2-@PointerPrev2) as varchar)
Set @PointerCurr3=CharIndex('|',@X_3,@PointerPrev3+1)
Set @TX_3 =cast(SUBSTRING(@X_3,@PointerPrev3,@PointerCurr3-@PointerPrev3) as float)
Set @PointerCurr4=CharIndex('|',@Y_4,@PointerPrev4+1)
Set @TY_4 =cast(SUBSTRING(@Y_4,@PointerPrev4,@PointerCurr4-@PointerPrev4) as float)
Set @PointerCurr5=CharIndex('|',@parts_5,@PointerPrev5+1)
Set @Tparts_5 =cast(SUBSTRING(@parts_5,@PointerPrev5,@PointerCurr5-@PointerPrev5) as int)
Insert into CFMap_Points ([ID],[FeatureID],[X],[Y], [parts]) Values(@TID_1,@TFeatureID_2,@TX_3,@TY_4, @Tparts_5)
SET @PointerPrev1= @PointerCurr1
SET @PointerPrev2= @PointerCurr2
SET @PointerPrev3= @PointerCurr3
SET @PointerPrev4= @PointerCurr4
SET @PointerPrev5= @PointerCurr5
while ( (@PointerPrev1+1 < LEN(@ID_1)) and (@PointerPrev2+1 < LEN(@FeatureID_2)) and (@PointerPrev3+1 < LEN(@X_3)) and (@PointerPrev4+1 < LEN(@Y_4)) and (@PointerPrev5+1 < LEN(@parts_5)) )
Begin
Set @PointerCurr1=CharIndex('|',@ID_1,@PointerPrev1+1)
Set @PointerCurr2=CharIndex('|',@FeatureID_2,@PointerPrev2+1)
Set @PointerCurr3=CharIndex('|',@X_3,@PointerPrev3+1)
Set @PointerCurr4=CharIndex('|',@Y_4,@PointerPrev4+1)
Set @PointerCurr5=CharIndex('|',@parts_5,@PointerPrev5+1)
if(@PointerCurr1>0 and @PointerCurr2>0 and @PointerCurr3>0 and @PointerCurr4>0 and @PointerCurr5>0)
Begin
Set @TID_1 =cast(SUBSTRING(@ID_1,@PointerPrev1+1,@PointerCurr1-@PointerPrev1-1) as varchar)
Set @TFeatureID_2 =cast(SUBSTRING(@FeatureID_2,@PointerPrev2+1,@PointerCurr2-@PointerPrev2-1) as varchar)
Set @TX_3 =cast(SUBSTRING(@X_3,@PointerPrev3+1,@PointerCurr3-@PointerPrev3-1) as float)
Set @TY_4 =cast(SUBSTRING(@Y_4,@PointerPrev4+1,@PointerCurr4-@PointerPrev4-1) as float)
Set @Tparts_5 =cast(SUBSTRING(@parts_5,@PointerPrev5+1,@PointerCurr5-@PointerPrev5-1) as int)
Insert into [mapx].[dbo].[CFMap_Points] ([ID],[FeatureID],[X],[Y], [parts]) Values(@TID_1,@TFeatureID_2,@TX_3,@TY_4, @Tparts_5)
SET @PointerPrev1 = @PointerCurr1
SET @PointerPrev2 = @PointerCurr2
SET @PointerPrev3 = @PointerCurr3
SET @PointerPrev4 = @PointerCurr4
SET @PointerPrev5 = @PointerCurr5
End
else
Break
End
Set @TID_1 =cast(SUBSTRING(@ID_1,@PointerPrev1+1,LEN(@ID_1)-@PointerPrev1) as varchar)
Set @TFeatureID_2 =cast(SUBSTRING(@FeatureID_2,@PointerPrev2+1,LEN(@FeatureID_2)-@PointerPrev2) as varchar)
Set @TX_3 =cast(SUBSTRING(@X_3,@PointerPrev3+1,LEN(@X_3)-@PointerPrev3) as float)
Set @TY_4 =cast(SUBSTRING(@Y_4,@PointerPrev4+1,LEN(@Y_4)-@PointerPrev4) as float)
Set @Tparts_5 =cast(SUBSTRING(@parts_5,@PointerPrev5+1,LEN(@parts_5)-@PointerPrev5) as int)
Insert into [mapx].[dbo].[CFMap_Points] ([ID],[FeatureID],[X],[Y], [parts]) Values(@TID_1,@TFeatureID_2,@TX_3,@TY_4, @Tparts_5) Set NoCount OFF
if @@error=0
begin
commit transaction
end
else
begin
rollback transaction
end
GO
解决方案 »
- 触发器
- 一个存储过程被几个客户同时调用会有什么后果?
- 我的IBM服务器昨晚停电后,就开不起来了,请各大虾帮忙
- 囡囡求助:谁能帮我解释下这些词啥意思哦!
- 关于sql2000里身份验证的问题。
- 在开一贴::事务复制中又出现一个错误!原因我应该找到了!同步的表名非法!例如表名为a-a:sql 2000
- 对于在ACCESS中的“是与否”的字段类型,在表导入了SQL SERVER中后,变成了类型为“bit"。
- 着急问阿,在线等啊!!!!SQL问题
- !!!请教SQL语句!!!
- plsql developer单步调试问题
- 关于substring()函数
- [急...]多表查询,当一个表没有符合条件的记录时,是不是总的查询结果也为空?
表结构points(id,fid,x,y,parts)
id ,fid 都是字符串 x,y都是浮点, parts是int
{AB6A7A55-DAC9-4093-BD65-1BB3A8C3AA45}|{7CCC6907-D10F-4200-89EB-DDD5F1021FB这个是我的原串,按照程序应该是{AB6A7A55-DAC9-4093-BD65-1BB3A8C3AA45}截取下来,但是现在每一个都是30个字符,也就是{AB6A7A55-DAC9-4093-BD65-1BB3A 就结束了,为什么?难道substring函数有长度限制?
-------------------------------------把每个变量都输出,在查询分析器中执行并查看输入的变量的值。
Set @PointerCurr1=CharIndex('|',@ID_1,@PointerPrev1+1)打出来 @PointerCurr1 = 39
@PointPrev1 = 1 Set @TID_1 =cast(SUBSTRING(@ID_1,@PointerPrev1,@PointerCurr1-@PointerPrev1) as varchar)打出来 @TID_1 = 30个字符的字符串问题就出在substring()了,截取的字符串长度只有30个,但是我带进去的参数是38个啊..郁闷