将数据类型 varchar 转换为 numeric 时出错
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO


ALTER PROCEDURE GetDlvNum
@Serial varchar(18),
@total INT OUT
AS
declare @CountSelect nvarchar(2000)
--设置统计查询语句

set @countSelect=N'SELECT @Count = COUNT(*) FROM tblDlvInfoback where Serial='+@Serial

--执行并返回总数
exec sp_executesql @CountSelect,N'@Count INT output',@total output

SET NOCOUNT ON
if @total<=0
begin
--查询示例

select DlvNo from OPENROWSET('MSDASQL', 'DRIVER={SQL Server};SERVER=192.168.1.198;UID=XXX;PWD=XXX', mydate.dbo.tblDlvInfo)
where Serial=@Serial

end
set nocount off





GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO


其中 @Serial是一串18位的 数字代码 运行后 总是提示我说
服务器: 消息 8114，级别 16，状态 5，行 1
将数据类型 varchar 转换为 numeric 时出错。




[解决办法]

SQL code

set @countSelect=N'SELECT @Count = COUNT(*)  FROM tblDlvInfoback where Serial='''+@Serial+''''