我的存储过程如下:
在ADO两层下正常,但在三层下(用socketconnection)出错,请大侠们指点
CREATE PROCEDURE SPmcFilterMat
@inNo char(4),
@attrNoStr char(100),
@attrValueStr nvarchar(250)
AS
--analyze attrNoStr, attrValueStr
declare @attrNo char(4),
@attrValue nvarchar(60),
@sInitSQL char(60),
@sOtherSQL nvarchar(600),
@iPos int,
@iPos2 int,
@iFirst int,
@sTemp nvarchar(1)
------------------------------------------------
/*Select matNo
from VmcMatMainAttrs
where inNo=@inNo
and attrNo=@attrNo
and attrValue=@attrValue
return
*/
-------------------------------------------------
Set @sTemp = '~'
Select @iPos=CharIndex(@sTemp, @attrNoStr)
if @iPos=0
begin
if ltrim(rtrim(@attrNoStr)) = ''
return
Select @attrNo = @attrNoStr
Select @attrValue = @attrValueStr
Select matNo, matName
from VmatName
where matNo in
(Select matNo
from VmcMatMainAttrs
where inNo = @inNo
and attrNo = @attrNo
and attrValue = @attrValue
)
return
end
else
begin
Select @iFirst = 1
while @iPos>0
begin
Select @attrNo = SubString(@attrNoStr, 1, @iPos-1)
Select @iPos2 = CharIndex(@sTemp, @attrValueStr)
Select @attrValue = SubString(@attrValueStr, 1, @iPos2-1) --这一句出错
Select @sInitSQL = ' Select matNo from VmcMatMainAttrs where inNo='+@inNo
if @iFirst=1
begin
Select @sOtherSQL = ' Select matNo from VmcMatMainAttrs where attrNo = ' + @attrNo +
' and attrValue = ''' +ltrim(rtrim(@attrValue)) + ''' and matNo in (' + @sInitSQL + ') '
Select @iFirst = 2
end
else
begin
Select @sOtherSQL = ' Select matNo from VmcMatMainAttrs where attrNo = ' + @attrNo +
' and attrValue = ''' + ltrim(rtrim(@attrValue)) + ''' and matNo in (' + rtrim(@sOtherSQL) + ') '
end
if @iPos=Len(@attrNoStr)
begin
Exec(@sOtherSQL)
return
end
else
begin
Select @attrNoStr = SubString(@attrNoStr, @iPos+1, Len(@attrNoStr)-@iPos)
Select @attrValueStr = SubString(@attrValueStr, @iPos2+1, Len(@attrValueStr)-@iPos2)
Select @iPos = CharIndex(@sTemp, @attrNoStr)
end
end
end
GO