执行以下代码可以正常显示结果,SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO select oc_date as 交易日期,
fund_account as 资金帐号,
ltrim(rtrim(client_name)) as 客户姓名,
stock_account as 股东代码,
client_group as 客户组别,
stock_code as 证券代码,
stock_name as 证券名称,
case
when entrust_bs = "1" then "买入"
when entrust_bs = "2" then "卖出"
end as 买卖类别,
case
when entrust_type = "0" then "委托"
when entrust_type = "1" then "查询"
when entrust_type = "2" then "撤单"
when entrust_type = "3" then "补单"
end as 委托类别,
case
when entrust_status = "0" then "未报"
when entrust_status = "1" then "待报"
when entrust_status = "2" then "已报"
when entrust_status = "3" then "已报待撤"
when entrust_status = "4" then "部成待撤"
when entrust_status = "5" then "部撤"
when entrust_status = "6" then "已撤"
when entrust_status = "7" then "部成"
when entrust_status = "8" then "已成"
when entrust_status = "9" then "废单"
when entrust_status = "B" then "未报"
when entrust_status = "W" then "待确认"
end as 委托状态,
sum(entrust_amount) as 申报数量,
sum(entrust_amount * entrust_price) as 委托金额
from his2008..entrust200810
where (oc_date = '20081028')
group by oc_date,
fund_account,
client_name,
stock_account,
client_group,
stock_code,
entrust_bs,
entrust_type,
entrust_status,
stock_name但把这一段写进存储过程之中,代入变量,将表名用变量代替,SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
ALTER PROC hisEntrustJour @BeginDate varchar(8),@FinishDate varchar(8)
AS IF ( EXISTS ( SELECT * FROM tempdb.dbo.sysobjects WHERE name = "##hisEntrustJour" ))
DROP TABLE ##hisEntrustJourdeclare @filename nvarchar(50) set @filename = "hshis" + left(@BeginDate,4) + "..hisfsjour" + left(@BeginDate,4) + right(left(@BeginDate,6),2) exec(' select oc_date as 交易日期,
fund_account as 资金帐号,
ltrim(rtrim(client_name)) as 客户姓名,
stock_account as 股东代码,
client_group as 客户组别,
stock_code as 证券代码,
stock_name as 证券名称,
case
when entrust_bs = "1" then "买入"
when entrust_bs = "2" then "卖出"
end as 买卖类别,
case
when entrust_type = "0" then "委托"
when entrust_type = "1" then "查询"
when entrust_type = "2" then "撤单"
when entrust_type = "3" then "补单"
end as 委托类别,
case
when entrust_status = "0" then "未报"
when entrust_status = "1" then "待报"
when entrust_status = "2" then "已报"
when entrust_status = "3" then "已报待撤"
when entrust_status = "4" then "部成待撤"
when entrust_status = "5" then "部撤"
when entrust_status = "6" then "已撤"
when entrust_status = "7" then "部成"
when entrust_status = "8" then "已成"
when entrust_status = "9" then "废单"
when entrust_status = "B" then "未报"
when entrust_status = "W" then "待确认"
end as 委托状态,
sum(entrust_amount) as 申报数量,
sum(entrust_amount * entrust_price) as 委托金额
into ##hisExchangeJour
from ' + @filename +
' where (oc_date between ' + @BeginDate + ' and ' + @FinishDate + ')
group by oc_date,
fund_account,
client_name,
stock_account,
client_group,
stock_code,
entrust_bs,
entrust_type,
entrust_status,
stock_name'
)
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO则报错:
Server: Msg 207, Level 16, State 3, Line 3
列名 'entrust_type' 无效。
Server: Msg 207, Level 16, State 1, Line 3
列名 'entrust_type' 无效。
Server: Msg 207, Level 16, State 1, Line 3
列名 'entrust_type' 无效。
Server: Msg 207, Level 16, State 1, Line 3
列名 'entrust_type' 无效。
Server: Msg 207, Level 16, State 1, Line 3
列名 'entrust_status' 无效。
Server: Msg 207, Level 16, State 1, Line 3
列名 'entrust_status' 无效。
Server: Msg 207, Level 16, State 1, Line 3
列名 'entrust_status' 无效。
Server: Msg 207, Level 16, State 1, Line 3
列名 'entrust_status' 无效。
Server: Msg 207, Level 16, State 1, Line 3
列名 'entrust_status' 无效。
Server: Msg 207, Level 16, State 1, Line 3
列名 'entrust_status' 无效。
Server: Msg 207, Level 16, State 1, Line 3
列名 'entrust_status' 无效。
Server: Msg 207, Level 16, State 1, Line 3
列名 'entrust_status' 无效。
Server: Msg 207, Level 16, State 1, Line 3
列名 'entrust_status' 无效。
Server: Msg 207, Level 16, State 1, Line 3
列名 'entrust_status' 无效。
Server: Msg 207, Level 16, State 1, Line 3
列名 'entrust_status' 无效。
Server: Msg 207, Level 16, State 1, Line 3
列名 'entrust_status' 无效。
Server: Msg 207, Level 16, State 1, Line 3
列名 'entrust_amount' 无效。
Server: Msg 207, Level 16, State 1, Line 3
列名 'entrust_type' 无效。
Server: Msg 207, Level 16, State 1, Line 3
列名 'entrust_status' 无效。
Server: Msg 207, Level 16, State 1, Line 3
列名 'entrust_amount' 无效。
Server: Msg 207, Level 16, State 1, Line 3
列名 'entrust_price' 无效。
Server: Msg 207, Level 16, State 1, Line 3
列名 'entrust_type' 无效。
Server: Msg 207, Level 16, State 1, Line 3
列名 'entrust_status' 无效。
不知道问题出在什么地方,奇怪啊。但不用变量代替表名,即
into ##hisExchangeJour
from ' + 'his2008..entrust200810' +
' where (oc_date between ' + @BeginDate + ' and ' + @FinishDate + ')
又正常了。是什么原因呢?
ALTER PROC hisEntrustJour @BeginDate varchar(8),@FinishDate varchar(8)
AS IF ( EXISTS ( SELECT * FROM tempdb.dbo.sysobjects WHERE name = "##hisEntrustJour" ))
DROP TABLE ##hisEntrustJourdeclare @filename nvarchar(50),@Sql Nvarchar(2000) set @filename = "hshis" + left(@BeginDate,4) + "..hisfsjour" + left(@BeginDate,4) + right(left(@BeginDate,6),2) Set @Sql = 'select oc_date as 交易日期,
fund_account as 资金帐号,
ltrim(rtrim(client_name)) as 客户姓名,
stock_account as 股东代码,
client_group as 客户组别,
stock_code as 证券代码,
stock_name as 证券名称,
case
when entrust_bs = "1" then "买入"
when entrust_bs = "2" then "卖出"
end as 买卖类别,
case
when entrust_type = "0" then "委托"
when entrust_type = "1" then "查询"
when entrust_type = "2" then "撤单"
when entrust_type = "3" then "补单"
end as 委托类别,
case
when entrust_status = "0" then "未报"
when entrust_status = "1" then "待报"
when entrust_status = "2" then "已报"
when entrust_status = "3" then "已报待撤"
when entrust_status = "4" then "部成待撤"
when entrust_status = "5" then "部撤"
when entrust_status = "6" then "已撤"
when entrust_status = "7" then "部成"
when entrust_status = "8" then "已成"
when entrust_status = "9" then "废单"
when entrust_status = "B" then "未报"
when entrust_status = "W" then "待确认"
end as 委托状态,
sum(entrust_amount) as 申报数量,
sum(entrust_amount * entrust_price) as 委托金额
into ##hisExchangeJour
from ' + @filename +
' where (oc_date between ''' + @BeginDate + ''' and ''' + @FinishDate + ''')
group by oc_date,
fund_account,
client_name,
stock_account,
client_group,
stock_code,
entrust_bs,
entrust_type,
entrust_status,
stock_name'
exec(@Sql)