以下代码语法没问题,但将print换成exec后,提示Incorrect syntax near 'substring'.是何原因?declare @BeginDate nvarchar(8), @FinishDate nvarchar(8), @sSqlstring nvarchar(4000)--set @BeginDate = convert(varchar(8), dateadd(day,-day(getdate())+1,getdate()), 112)
set @BeginDate = '20081201'
--select @FinishDate = sysdate from fornia..sysconfig
set @FinishDate = '20081226'delete yxh where sysdate between @BeginDate and @FinishDate print('select a.orgid as 机构代码, a.sysdate as 日期, cast(a.opendate as char(8)) as 开户日期, a.fundid as 客户代码,
max(case a.moneytype when ' + '''0''' + ' then (a.fundbal) * 1 else 0 end) + max(case a.moneytype when ' + '''1''' + ' then (a.fundbal) * isnull(b.hkd, 0.8767) else 0 end) + max(case a.moneytype when ' + '''2''' + ' then (a.fundbal) * isnull(b.usd, 6.8315) else 0 end) as 资金余额,
max(case a.moneytype when ' + '''0''' + ' then (a.mktval) * 1 else 0 end) + max(case a.moneytype when ' + '''1''' + ' then (a.mktval) * isnull(b.hkd, 0.8767) else 0 end) + max(case a.moneytype when ' + '''2''' + ' then (a.mktval) * isnull(b.usd, 6.8315) else 0 end) as 证券市值,
max(case a.moneytype when ' + '''0''' + ' then (a.fundbal + a.mktval) else 0 end) as 人民币资产,
max(case a.moneytype when ' + '''1''' + ' then (a.fundbal + a.mktval) else 0 end) as 港币资产,
max(case a.moneytype when ' + '''2''' + ' then (a.fundbal + a.mktval) else 0 end) as 美元资产,
max(case a.moneytype when ' + '''0''' + ' then (a.fundbal + a.mktval) * 1 else 0 end) + max(case a.moneytype when ' + '''1''' + ' then (a.fundbal + a.mktval) * isnull(b.hkd, 0.8767) else 0 end) + max(case a.moneytype when ' + '''2''' + ' then (a.fundbal + a.mktval) * isnull(b.usd, 6.8315) else 0 end) as 总资产
into #T
from asset_' + substring(@BeginDate, 1, 6) + ' a
left join ExcRate b
on a.sysdate = b.bizdate
where a.fundid in (select fundid from v_cmCustmer)
group by a.orgid, a.fundid, a.sysdate, a.opendate')
set @strsql='your cmd'
exec sp_executesql @strsql注意:下面两个字符间应该有个空格
' a
set @BeginDate = '20081201'
--select @FinishDate = sysdate from fornia..sysconfig
set @FinishDate = '20081226'delete yxh where sysdate between @BeginDate and @FinishDate print('select a.orgid as 机构代码, a.sysdate as 日期, cast(a.opendate as char(8)) as 开户日期, a.fundid as 客户代码,
max(case a.moneytype when ' + '''0''' + ' then (a.fundbal) * 1 else 0 end) + max(case a.moneytype when ' + '''1''' + ' then (a.fundbal) * isnull(b.hkd, 0.8767) else 0 end) + max(case a.moneytype when ' + '''2''' + ' then (a.fundbal) * isnull(b.usd, 6.8315) else 0 end) as 资金余额,
max(case a.moneytype when ' + '''0''' + ' then (a.mktval) * 1 else 0 end) + max(case a.moneytype when ' + '''1''' + ' then (a.mktval) * isnull(b.hkd, 0.8767) else 0 end) + max(case a.moneytype when ' + '''2''' + ' then (a.mktval) * isnull(b.usd, 6.8315) else 0 end) as 证券市值,
max(case a.moneytype when ' + '''0''' + ' then (a.fundbal + a.mktval) else 0 end) as 人民币资产,
max(case a.moneytype when ' + '''1''' + ' then (a.fundbal + a.mktval) else 0 end) as 港币资产,
max(case a.moneytype when ' + '''2''' + ' then (a.fundbal + a.mktval) else 0 end) as 美元资产,
max(case a.moneytype when ' + '''0''' + ' then (a.fundbal + a.mktval) * 1 else 0 end) + max(case a.moneytype when ' + '''1''' + ' then (a.fundbal + a.mktval) * isnull(b.hkd, 0.8767) else 0 end) + max(case a.moneytype when ' + '''2''' + ' then (a.fundbal + a.mktval) * isnull(b.usd, 6.8315) else 0 end) as 总资产
into #T
from asset_' + substring(convert(varchar(10),@BeginDate,120), 1, 6) + ' a
left join ExcRate b
on a.sysdate = b.bizdate
where a.fundid in (select fundid from v_cmCustmer)
group by a.orgid, a.fundid, a.sysdate, a.opendate')
set @BeginDate = '20081201'
--select @FinishDate = sysdate from fornia..sysconfig
set @FinishDate = '20081226'delete yxh where sysdate between @BeginDate and @FinishDate
declare @sql varchar(8000)
set @sql='select a.orgid as 机构代码, a.sysdate as 日期, cast(a.opendate as char(8)) as 开户日期, a.fundid as 客户代码,
max(case a.moneytype when ' + '''0''' + ' then (a.fundbal) * 1 else 0 end) + max(case a.moneytype when ' + '''1''' + ' then (a.fundbal) * isnull(b.hkd, 0.8767) else 0 end) + max(case a.moneytype when ' + '''2''' + ' then (a.fundbal) * isnull(b.usd, 6.8315) else 0 end) as 资金余额,
max(case a.moneytype when ' + '''0''' + ' then (a.mktval) * 1 else 0 end) + max(case a.moneytype when ' + '''1''' + ' then (a.mktval) * isnull(b.hkd, 0.8767) else 0 end) + max(case a.moneytype when ' + '''2''' + ' then (a.mktval) * isnull(b.usd, 6.8315) else 0 end) as 证券市值,
max(case a.moneytype when ' + '''0''' + ' then (a.fundbal + a.mktval) else 0 end) as 人民币资产,
max(case a.moneytype when ' + '''1''' + ' then (a.fundbal + a.mktval) else 0 end) as 港币资产,
max(case a.moneytype when ' + '''2''' + ' then (a.fundbal + a.mktval) else 0 end) as 美元资产,
max(case a.moneytype when ' + '''0''' + ' then (a.fundbal + a.mktval) * 1 else 0 end) + max(case a.moneytype when ' + '''1''' + ' then (a.fundbal + a.mktval) * isnull(b.hkd, 0.8767) else 0 end) + max(case a.moneytype when ' + '''2''' + ' then (a.fundbal + a.mktval) * isnull(b.usd, 6.8315) else 0 end) as 总资产
into #T
from asset_' + substring(@BeginDate, 1, 6) + ' a
left join ExcRate b
on a.sysdate = b.bizdate
where a.fundid in (select fundid from v_cmCustmer)
group by a.orgid, a.fundid, a.sysdate, a.opendate'
exec(@sql)
你的只是语句应该用 sp_executesql 来执行