SQL语法转换错误与神秘数据

Question

我在 SQL Server 2000 存储过程中收到此消息：

将 varchar 值 '........................' 转换为 a 时出现语法错误 数据类型为 int 的列。

基本上我正在尝试插入数据并在现有的存储过程中使用它，因此我可能插入了错误的数据，但我没有插入'............ ' 任何地方，我都找不到相关的地方。

问题是，我在查询的表中找不到 '............'。找到此错误的来源？

编辑

部分存储过程引发错误：

  Select @iFromLocationID = IsNull(RecID,0)
    From InventoryLocations 
   Where LocItemNumber = @vItemNumber
     And IsNull(SkidNumber,'') <> ''
     And Cast(SkidNumber as int) = @iSkidFlagID
     And Warehouse = @cFromWarehouse
     And Aisle = @cFromAisle 
     And Slot = @cFromSlot 
     And locLevel = @cFromLevel 
     And Bin = @cFromBin 

InventoryLocations 的表架构：

CREATE TABLE [dbo].[InventoryLocations](
    [recid] [int] IDENTITY(1,1) NOT NULL,
    [LocItemNumber] [char](16) NOT NULL,
    [WareHouse] [char](2) NOT NULL,
    [Aisle] [char](3) NOT NULL,
    [Slot] [char](3) NOT NULL,
    [locLevel] [char](2) NOT NULL,
    [Bin] [char](2) NOT NULL,
    [Extra] [char](2) NOT NULL,
    [LocNumber] [char](2) NOT NULL,
    [RollNumber] [char](20) NOT NULL,
    [QuickRoll] [int] NOT NULL,
    [SkidNumber] [char](15) NOT NULL,
    [RollsInStock] [int] NOT NULL,
    [LocQtyOnHand] [float] NOT NULL,
    [LocQtyOnOrder] [float] NOT NULL,
    [LocQtyCommited] [float] NOT NULL,
    [TotalReceived] [float] NOT NULL,
    [TotalIssued] [float] NOT NULL,
    [TotalDollars] [float] NOT NULL,
    [Capacity] [float] NOT NULL,
    [AvailableSpace] [float] NOT NULL,
    [bkey0] [char](30) NULL,
    [bkey1] [char](30) NULL,
    [bkey2] [char](30) NULL,
    [bkey3] [char](14) NULL,
    [LastPhysicalCountDate] [datetime] NULL,
    [LastCycleCountDate] [datetime] NULL,
    [EnteredBy] [varchar](50) NULL,
    [EnteredDateTime] [datetime] NULL,
 CONSTRAINT [IX_InventoryLocations_1] UNIQUE NONCLUSTERED 
(
    [LocItemNumber] ASC,
    [WareHouse] ASC,
    [Aisle] ASC,
    [Slot] ASC,
    [locLevel] ASC,
    [Bin] ASC,
    [Extra] ASC,
    [RollNumber] ASC,
    [SkidNumber] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

Answer 1

如果您正在构建动态 SQL，请尝试在执行之前打印查询。如果没有，请尝试在编辑器中单独运行存储过程中的查询。如果您有 INSERT..SELECT，请尝试仅运行查询的 SELECT 部分。

Answer 2

这一定是问题所在：

 And IsNull(SkidNumber,'') <> ''
 And Cast(SkidNumber as int) = @iSkidFlagID

尝试运行以下命令，看看是否出现错误：

SELECT CAST(SkidNumber as INT)
FROM InventoryLocations

由于 @iSkidFlagID 是 INT 并且 SkidNumber 是 CHAR(15)，因此它必须是错误发生的地方。

Answer 3

看起来 null 值导致 SQL 返回“.................”。我已经更改了要插入的值，现在我不再收到该错误了。