转换 varchar 值“*”时转换失败数据类型为 int

Question

当我在 sql server 2005 中运行嵌套 while 循环时，我遇到了这个问题。

我的外循环得到一次迭代，然后我的内循环得到它的完整第一次迭代，但我的内循环之后的语句永远不会被执行，然后似乎打破了一切。

我现在迷路了，我觉得我错过了一些很容易的东西，非常感谢任何帮助。

while exists(select top 1 ident from #tmpAttorneyImport (nolock) where parsed = 0 and zipcode <> '')
begin

set @intCurrentIdent = 0
set @vcrCurrentAttonreyName = ''
set @vcrCurrentZip = ''

select top 1 @intCurrentIdent = ident from #tmpAttorneyImport (nolock) where parsed = 0
select @vcrCurrentAttonreyName = ltrim(rtrim(attorneyname)) from #tmpAttorneyImport (nolock) where ident = @intCurrentIdent
select @vcrCurrentZip = ltrim(rtrim(zipcode)) from #tmpAttorneyImport (nolock) where ident = @intCurrentIdent

if(len(@vcrCurrentZip) > 3)
 begin

    set @vcrMinZip = ''
    set @vcrMaxZip = ''

    select @vcrMinZip = ltrim(rtrim(left(@vcrCurrentZip, 3)))
    select @vcrMaxZip = ltrim(rtrim(right(@vcrCurrentZip, 3)))

    while(convert(int, @vcrMinZip) <= convert(int, @vcrMaxZip)) -- sql is telling me this line has the error
     begin

        insert into #tmpAttorneysFormatted(
            attorneyname,
            zipcode
        )
        select
            attorneyname = @vcrCurrentAttonreyName,
            zipcode = case
                        when len(@vcrMinZip) = 1 then '00' + ltrim(rtrim(@vcrMinZip))
                        when len(@vcrMinZip) = 2 then '0' + ltrim(rtrim(@vcrMinZip))
                        when len(@vcrMinZip) = 3 then ltrim(rtrim(@vcrMinZip))
                      end

        select @vcrMinZip = convert(int, @vcrMinZip) + 1        

     end

    -- this statement does not get hit
    update #tmpAttorneyImport
    set
        parsed = 1
    where
        ident = @intCurrentIdent

 end
else
 begin

    insert into #tmpAttorneysFormatted(
        attorneyname,
        zipcode
    )
    select
        attorneyname = @vcrCurrentAttonreyName,
        zipcode = case
                    when len(@vcrCurrentZip) = 1 then '00' + ltrim(rtrim(@vcrCurrentZip))
                    when len(@vcrCurrentZip) = 2 then '0' + ltrim(rtrim(@vcrCurrentZip))
                    when len(@vcrCurrentZip) = 3 then ltrim(rtrim(@vcrCurrentZip))
                  end

        update #tmpAttorneyImport
        set
            parsed = 1
        where
            ident = @intCurrentIdent

 end

结尾

Answer 1

select @vcrMinZip = ltrim(rtrim(left(@vcrCurrentZip, 3)))
select @vcrMaxZip = ltrim(rtrim(right(@vcrCurrentZip, 3)))

您如何确定您的数据是干净的？

我将（在此之后）放入两行：

print @vcrMinZip
print @vcrMaxZip

并查看实际从字符串中解析出的内容。