在将 varchar 值 ''' 转换成数据类型 int 时失败。解决方案

2013-08-11

在将 varchar 值 ','' 转换成数据类型 int 时失败。写了一个存储过程出现如上错误：在将 var

在将 varchar 值 ','' 转换成数据类型 int 时失败。
写了一个存储过程出现如上错误：在将 varchar 值 ','' 转换成数据类型 int 时失败。
创建数据表sql如下：


CREATE TABLE [dbo].[hx_business_record20110901](
[hx_business_id] [bigint] NOT NULL,
[hx_business_account] [nvarchar](25) NULL,
[hx_prepaid_password] [nvarchar](100) NULL,
[hx_prepaid_flag] [int] NULL,
[hx_business_transaction] [nvarchar](10) NULL,
[hx_business_money] [nvarchar](12) NULL,
[hx_business_money_flag] [nvarchar](20) NULL,
[hx_business_datetime] [nvarchar](25) NULL,
[hx_business_mark] [nvarchar](25) NULL,
[hx_merchant_number] [nvarchar](20) NULL,
[hx_merchant_category] [int] NULL,
[hx_business_accept] [nvarchar](10) NULL,
[hx_card_number] [nvarchar](10) NULL,
[hx_business_createtime] [datetime] NULL,
[hx_business_status] [int] NULL,
[hx_business_content] [nvarchar](10) NULL,
[hx_business_distribution] [int] NULL
) ON [PRIMARY]
GO

创建存储过程如下：


ALTER PROCEDURE [dbo].[hx_business_record_ADD] 
@tableName varchar(50),
    @hx_business_id bigint,
@hx_business_account nvarchar(25),
@hx_prepaid_password nvarchar(100),
@hx_prepaid_flag int,
@hx_business_transaction nvarchar(10),
@hx_business_money nvarchar(10),
@hx_business_money_flag nvarchar(20),
@hx_business_datetime nvarchar(25),
@hx_business_mark nvarchar(25),
@hx_merchant_number nvarchar(20),
@hx_merchant_category int,
@hx_business_accept nvarchar(10),
@hx_card_number nvarchar(10),
@hx_business_createtime datetime,
@hx_business_status int,
@hx_business_content nvarchar(10),
@hx_business_distribution int   
AS
BEGIN
SET NOCOUNT ON;
        begin transaction
--插入记录 
declare @sql nvarchar(max)
set @sql = N'INSERT INTO '+@tableName+'
(
[hx_business_id],[hx_business_account],[hx_prepaid_password],
[hx_prepaid_flag],[hx_business_transaction],[hx_business_money],
[hx_business_money_flag],[hx_business_datetime],[hx_business_mark],
[hx_merchant_number],[hx_merchant_category],[hx_business_accept],
[hx_card_number],[hx_business_createtime],[hx_business_status],
[hx_business_content],[hx_business_distribution]
 )
 VALUES
 (  '+ltrim(@hx_business_id)+',
'''+isnull(@hx_business_account,null)+''', 
 
'''+isnull(@hx_prepaid_password,null)+''',
'+isnull(@hx_prepaid_flag),null)+',
'''+isnull(@hx_business_transaction,null)+''',
'''+isnull(@hx_business_money,null)+''',
'''+isnull(@hx_business_money_flag,null)+''',
'''+isnull(@hx_business_datetime,null)+''',
'''+isnull(@hx_business_mark,null)+''',
'''+isnull(@hx_merchant_number,null)+''',
'+isnull(@hx_merchant_category,null)+',
'''+isnull(@hx_business_accept,null)+''',
'''+isnull(@hx_card_number,null)+''',
'''+isnull(convert(varchar(20),@hx_business_createtime,120),null)+''',
'+isnull(@hx_business_status,null)+',
'''+isnull(@hx_business_content,null)+''',
'+isnull(@hx_business_distribution,null)+')'

 print @sql
 exec sp_executesql @sql
 IF @@error=0
   begin
 COMMIT TRANSACTION
 return 1
   end
 ELSE
   begin
 ROLLBACK TRANSACTION
 return 0
   end
        end

测试语句如下：


exec hx_business_record_ADD
'hx_business_record20110901'
,15489980
,'0809300527149141376'
,null
,null
,'B0010'
,'000000027494'
,null
,'0901150908'
,'0936928139'
,'000000000000038'
,null
,null
,'0000000078'
,null
,0
,null
,0

所报异常如下：
消息 245，级别 16，状态 1，过程 hx_business_record_ADD，第 44 行
在将 varchar 值 ',
'' 转换成数据类型 int 时失败。
弄了半天，没有弄明白，请各位老大帮帮我啊，呜呜
[解决办法]
动态SQL,拼写错了吧?

参考:--动态sql语句基本语法

1 :普通SQL语句可以用Exec执行

eg: Select * from tableName
Exec('select * from tableName')
Exec sp_executesql N'select * from tableName' -- 请注意字符串前一定要加N

2:字段名，表名，数据库名之类作为变量时，必须用动态SQL

eg:
declare @fname varchar(20)
set @fname = 'FiledName'
Select @fname from tableName -- 错误,不会提示错误，但结果为固定值FiledName,并非所要。

Exec('select ' + @fname + ' from tableName') -- 请注意加号前后的单引号的边上加空格

当然将字符串改成变量的形式也可
declare @fname varchar(20)
set @fname = 'FiledName' --设置字段名

declare @s varchar(1000)
set @s = 'select ' + @fname + ' from tableName'
Exec(@s) -- 成功
exec sp_executesql @s -- 此句会报错

declare @s Nvarchar(1000) -- 注意此处改为nvarchar(1000)
set @s = 'select ' + @fname + ' from tableName'
Exec(@s) -- 成功
exec sp_executesql @s -- 此句正确

3. 输出参数
declare @num int,
@sqls nvarchar(4000)
set @sqls='select count(*) from tableName'
exec(@sqls)
--如何将exec执行结果放入变量中？

declare @num int,
@sqls nvarchar(4000)
set @sqls='select @a=count(*) from tableName '
exec sp_executesql @sqls,N'@a int output',@num output
select @num

[解决办法]

                         VALUES
                         (  '+ltrim(@hx_business_id)+',
                            '''+isnull(@hx_business_account,null)+''',
                            '''+isnull(@hx_prepaid_password,null)+''',
                            '+isnull(@hx_prepaid_flag,null)+',             --这中间逗号前多了个右括号
                            '''+isnull(@hx_business_transaction,null)+''',
                            '''+isnull(@hx_business_money,null)+''',
                            '''+isnull(@hx_business_money_flag,null)+''',

                            '''+isnull(@hx_business_datetime,null)+''',
                            '''+isnull(@hx_business_mark,null)+''',
                            '''+isnull(@hx_merchant_number,null)+''',
                            '+isnull(@hx_merchant_category,null)+',
                            '''+isnull(@hx_business_accept,null)+''',
                            '''+isnull(@hx_card_number,null)+''',
                            '''+isnull(convert(varchar(20),@hx_business_createtime,120),null)+''',
                            '+isnull(@hx_business_status,null)+',
                            '''+isnull(@hx_business_content,null)+''',
                            '+isnull(@hx_business_distribution,null)+')'

[解决办法]
太奇怪了!
你为什么要
isnull(@hx_prepaid_password,null)
?
isnull函数就是要将null值转换为一个别的值,你把它还用NULL,那要用这个函数干嘛!
而且,你把这些可能含有NULL的值放到字串连接表达式里,一不小心就可能将整个表达式都算成NULL.

正确的写法是,如果对应的字段是字符型的,那就用四个引号代替那儿的NULL,如果是数值型的,则为0.
大概是:

                         (  '+ltrim(@hx_business_id)+',
                            '''+isnull(@hx_business_account,'''')+''',
                            '''+isnull(@hx_prepaid_password,'''')+''',

                            '+isnull(@hx_prepaid_flag,0)+',             --这中间逗号前多了个右括号
                            '''+isnull(@hx_business_transaction,'''')+''',
                            '''+isnull(@hx_business_money,'''')+''',
                            '''+isnull(@hx_business_money_flag,'''')+''',
                            '''+isnull(@hx_business_datetime,null)+''',  --日期只能为NULL这个isnull不用
                            '''+isnull(@hx_business_mark,'''')+''',
                            '''+isnull(@hx_merchant_number,'''')+''',
                            '+isnull(@hx_merchant_category,0)+',
                            '''+isnull(@hx_business_accept,'''')+''',
                            '''+isnull(@hx_card_number,'''')+''',
                            '''+isnull(convert(varchar(20),@hx_business_createtime,120),null)+''',
                            '+isnull(@hx_business_status,0)+',
                            '''+isnull(@hx_business_content,'''')+''',
                            '+isnull(@hx_business_distribution,0)+')'

当然,如果本来要的就是NULL,那把isnull函数去掉.
[解决办法]
如果本来就是NULL,应该:

isnull(@hx_prepaid_password,'NULL')

select '''a'','+isnull(null,'null')+',''b'''

/*
------------
'a',null,'b'

(1 行受影响)
*/

[解决办法]
这样拼接很不方便，建议使用函数。如下。



alter function fn_dynNumber( @in int)
returns varchar(max) 
as 
begin
DECLARE @out varchar(max) 
select @out = case 
when @in is null then 'null'
else ''''+convert(varchar(max), @in)+''''
end

return @out   
end

go

select dbo.fn_dynNumber(0) + ', ' + dbo.fn_dynNumber(null)
select  dbo.fn_dynNumber(null)
select dbo.fn_dynNumber(0) 
GO

alter function fn_dynString( @in varchar(max))
returns varchar(max) 
as 
begin
DECLARE @out varchar(max) 
select @out = case 
when @in is null then 'null'
else ''''+@in+''''
end

return @out   
end
GO

select dbo.fn_dynString('aa') + ', ' + dbo.fn_dynString(null)
select  dbo.fn_dynString(null)
select dbo.fn_dynString('aa') 
GO

热点排行

SQL Server

在将 varchar 值 ''' 转换成数据类型 int 时失败。解决方案