drop PROC Pro_GetNum
go
CREATE PROC Pro_GetNum
@InNum numeric(18, 3),
@Unit varchar(20),
@ResNo nvarchar(20),
@Num varchar(20) OUT ----返回ID
AS
DECLARE @SQL NVARCHAR(500) --执行的SQL
begin
if not exists(SELECT 1
FROM dbo.URes INNER JOIN
dbo.Unit ON dbo.URes.FUnit = dbo.Unit.FID where Unit.FUnit=@Unit and FResNo=@ResNo)
--单位不同,进行转换
set @SQL='SELECT cast( dbo.f_calc(ltrim('''+@InNum+''')+ltrim(Right(FExp,len(FExp)-4))) as DECIMAL(18,2))
FROM dbo.URes LEFT OUTER JOIN
dbo.UnitConvert ON dbo.URes.FUnitConvertID = dbo.UnitConvert.id
where FResNo='''+@ResNo+''''
else
--单位相同
set @SQL='select '''+@InNum+''' '
EXEC sp_executesql @SQL,N'@Num numeric(18, 3) OUT',@Num OUT
end
GO DECLARE @Num numeric(18, 3)
EXEC Pro_GetNum 100,'只','BZ00001',@Num OUT 服务器: 消息 8114,级别 16,状态 5,过程 Pro_GetNum,行 19
将数据类型 varchar 转换为 numeric 时出错。
你过程里定义的是varchar 嘛返回的时候 EXEC sp_executesql @SQL,N'@Num numeric(18, 3) OUT',@Num OUT
这么是numberic 了?
@Num varchar(20) OUT
AS
BEGIN
SELECT @num = 'test'
ENDDECLARE @Num numeric(18, 3)
EXEC Pro_GetNum @Num OUT SELECT @Num
CREATE PROC Pro_GetNum
@InNum numeric(18, 3),
@Unit varchar(20),
@ResNo nvarchar(20),
@Num numeric(18, 3) OUT ----返回ID
AS
DECLARE @SQL NVARCHAR(500) --执行的SQL
begin
if not exists(SELECT 1
FROM dbo.URes INNER JOIN
dbo.Unit ON dbo.URes.FUnit = dbo.Unit.FID where Unit.FUnit=@Unit and FResNo=@ResNo)
--单位不同,进行转换
set @SQL='SELECT cast( dbo.f_calc(ltrim('''+@InNum+''')+ltrim(Right(FExp,len(FExp)-4))) as DECIMAL(18,2))
FROM dbo.URes LEFT OUTER JOIN
dbo.UnitConvert ON dbo.URes.FUnitConvertID = dbo.UnitConvert.id
where FResNo='''+@ResNo+''''
else
--单位相同
set @SQL='select '''+@InNum+''' ' --好像是这里转换出错,还有上一句的@InNum,这怎么写
EXEC sp_executesql @SQL,N'@Num numeric(18, 3) OUT',@Num OUT
end
GO DECLARE @Num numeric(18, 3)
EXEC Pro_GetNum 8.45,'只','GD00001',@Num OUT
先
print @SQL 看看结果是什么。