高手帮忙看看这个存储过程！参数正确带进去，数据库不能插入...
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_4float
DECLARE @Tparts_5int
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
[解决办法]
好长啊。
[解决办法]
在存储过程中加print ,看看各变量是否有值。
中间加select * from 表，是否中间插入了，有来又回滚了。
[解决办法]
不知道该在什么地方看。
------------------------------
在查询分析器中执行并查看输入的变量的值。
[解决办法]
这个是我的原串，按照程序应该是{AB6A7A55-DAC9-4093-BD65-1BB3A8C3AA45}截取下来，但是现在每一个都是30个字符，也就是{AB6A7A55-DAC9-4093-BD65-1BB3A 就结束了，为什么？
难道substring函数有长度限制？
-------------------------------------
把每个变量都输出，在查询分析器中执行并查看输入的变量的值。
