--修改为: exec('update '+@GypYsDataTableName+' set bdj1='+@bdj1+', bdj2='+@bdj2+', jdj='+@jdj+', tdj='+@tdj+', re='''+@re+''' where ssdh='''+@ssdh+''' and qydh='''+@qydh+''' and cpdm='''+@cpdm+''' and InputYearMonth='''+@InputYearMonth+'''')
exec('update '+@GypYsDataTableName+' set bdj1='+@bdj1+', bdj2='+@bdj2+', jdj='+@jdj+', tdj='+@tdj+', re='+@re+' where ssdh='+@ssdh+' and qydh='+@qydh+' and cpdm='+@cpdm+' and InputYearMonth='+@InputYearMonth)--改为: exec('update '+@GypYsDataTableName+' set bdj1='+ltrim(@bdj1)+', bdj2='+ltrim(@bdj2)+', jdj='+ltrim(@jdj)+', tdj='+ltrim(@tdj)+', re='''+@re+''' where ssdh='''+@ssdh+''' and qydh='''+@qydh+''' and cpdm='''+@cpdm+''' and InputYearMonth='''+@InputYearMonth+'''')
[好像与楼上几位的回答不符exec('update '+@GypYsDataTableName+' set bdj1='+@bdj1+', bdj2='+@bdj2+', jdj='+@jdj+', tdj='+@tdj+', re='+@re+' where ssdh='+@ssdh+' and qydh='+@qydh+' and cpdm='+@cpdm+' and InputYearMonth='+@InputYearMonth) 只要将where前面的', re='+@re+去掉就可以正常执行。 那么问题也就不和楼上两位讲的数据类型和引号问题相关了
exec('update '+@GypYsDataTableName+' set bdj1='+@bdj1+', bdj2='+@bdj2+', jdj='+@jdj+', tdj='+@tdj+', re='''+@re+''' where ssdh='+@ssdh+' and qydh='+@qydh+' and cpdm='+@cpdm+' and InputYearMonth='+@InputYearMonth)事实上就是这样,你的写法代入后成这样了:update ta set ...,re= 这是个试验 where ssdh=.... 对于上面这个 这是个试验 SQL当作列来认的,当然不存update ta set ...,re= '这是个试验' where ssdh=....
那为何其中的@ssdh,@qydh,却可以呢 如果按您的意思,那这个语句变量带入后应该是 update GypGys set bdj1=123,....,re=这是个实验 where ssdh=4303 and qydh=0002 and ...而在此表结构中,字段ssdh,qydh我定义为char(4),那么这个语句正确的应该是: update GypGys set bdj1=123,....,re='这是个实验' where ssdh='4303' and qydh='0002' and ...
根据类型来拼串: 当是数值型是 '....'+ ltrim(@int)+',......' 当是字符型时 '....'''+@char +''',....' declare @GypYsDataTableName char(6) declare @InputYearMonth char(6) declare @ssdh char(4) declare @qydh char(4) declare @cpdm char(8) declare @bdj1 numeric(10,2) declare @bdj2 numeric(10,2) declare @jdj numeric(10,2) declare @tdj numeric(10,2) declare @re nvarchar(400) set @GypYsDataTableName='GypGys' set @InputYearMonth='200706' set @ssdh='4303' set @qydh='0002' set @cpdm='06100201' set @bdj1=123 set @bdj2=456 set @jdj=495 set @tdj=430 set @re='这是个试验'exec('update '+@GypYsDataTableName+' set bdj1='+ltrim(@bdj1)+', bdj2='+ltrim(@bdj2)+', jdj='+ltrim(@jdj)+', tdj='+ltrim(@tdj)+', re='''+@re+''' where ssdh='''+@ssdh+''' and qydh='''+@qydh+''' and cpdm='''+@cpdm+''' and InputYearMonth='''+@InputYearMonth+'''')
一般情况下你可以先把 exec 换成 print ,输出来看看,是不是你想要的呀
exec('update '+@GypYsDataTableName+' set bdj1='+ltrim(@bdj1)+', bdj2='+ltrim(@bdj2)+', jdj='+ltrim(@jdj)+', tdj='+ltrim(@tdj)+', re='''+@re+''' where ssdh='''+@ssdh+''' and qydh='''+@qydh+''' and cpdm='''+@cpdm+''' and InputYearMonth='''+@InputYearMonth+'''')为何最后是四个''''而不是两个''
->
bdj1='+convert(varchar,@bdj1)+', bdj2='+convert(varchar,@bdj2)+
', jdj='+convert(varchar,@jdj)+', tdj='+convert(varchar,@tdj)+',这些参数都是numeric类型的
你要转成字段型的才可以
->
'bdj1='+convert(varchar,@bdj1)+', bdj2='+convert(varchar,@bdj2)+
', jdj='+convert(varchar,@jdj)+', tdj='+convert(varchar,@tdj)+','
exec('update '+@GypYsDataTableName+' set bdj1='+@bdj1+', bdj2='+@bdj2+', jdj='+@jdj+', tdj='+@tdj+', re='+@re+' where ssdh='+@ssdh+' and qydh='+@qydh+' and cpdm='+@cpdm+' and InputYearMonth='+@InputYearMonth)--改为:
exec('update '+@GypYsDataTableName+' set bdj1='+ltrim(@bdj1)+', bdj2='+ltrim(@bdj2)+', jdj='+ltrim(@jdj)+', tdj='+ltrim(@tdj)+', re='''+@re+'''
where ssdh='''+@ssdh+''' and qydh='''+@qydh+''' and cpdm='''+@cpdm+''' and InputYearMonth='''+@InputYearMonth+'''')
只要将where前面的', re='+@re+去掉就可以正常执行。
那么问题也就不和楼上两位讲的数据类型和引号问题相关了
set ...,re= 这是个试验
where ssdh=....
对于上面这个 这是个试验 SQL当作列来认的,当然不存update ta
set ...,re= '这是个试验'
where ssdh=....
那为何其中的@ssdh,@qydh,却可以呢
如果按您的意思,那这个语句变量带入后应该是
update GypGys set bdj1=123,....,re=这是个实验 where ssdh=4303 and qydh=0002 and ...而在此表结构中,字段ssdh,qydh我定义为char(4),那么这个语句正确的应该是:
update GypGys set bdj1=123,....,re='这是个实验' where ssdh='4303' and qydh='0002' and ...
当是数值型是 '....'+ ltrim(@int)+',......'
当是字符型时 '....'''+@char +''',....'
declare @GypYsDataTableName char(6)
declare @InputYearMonth char(6)
declare @ssdh char(4)
declare @qydh char(4)
declare @cpdm char(8)
declare @bdj1 numeric(10,2)
declare @bdj2 numeric(10,2)
declare @jdj numeric(10,2)
declare @tdj numeric(10,2)
declare @re nvarchar(400)
set @GypYsDataTableName='GypGys'
set @InputYearMonth='200706'
set @ssdh='4303'
set @qydh='0002'
set @cpdm='06100201'
set @bdj1=123
set @bdj2=456
set @jdj=495
set @tdj=430
set @re='这是个试验'exec('update '+@GypYsDataTableName+' set bdj1='+ltrim(@bdj1)+', bdj2='+ltrim(@bdj2)+', jdj='+ltrim(@jdj)+', tdj='+ltrim(@tdj)+', re='''+@re+''' where ssdh='''+@ssdh+''' and qydh='''+@qydh+''' and cpdm='''+@cpdm+''' and InputYearMonth='''+@InputYearMonth+'''')
exec('update '+@GypYsDataTableName+' set bdj1='+ltrim(@bdj1)+', bdj2='+ltrim(@bdj2)+', jdj='+ltrim(@jdj)+', tdj='+ltrim(@tdj)+', re='''+@re+''' where ssdh='''+@ssdh+''' and qydh='''+@qydh+''' and cpdm='''+@cpdm+''' and InputYearMonth='''+@InputYearMonth+'''')为何最后是四个''''而不是两个''