高手进！困惑已久的存储过程建动态临时表问题

Question

String sqlParam = “select accountName,WDName ,'month0_Amount'=(month0_Amount * r.rateNumber)/10000 ,'month1_Amount'=(month1_Amount * r.rateNumber)/10000 ,'month2_Amount'=(month2_Amount * r.rateNumber)/10000 ,'month3_Amount'=(month3_Amount * r.rateNumber)/10000 ,'month4_Amount'=(month4_Amount * r.rateNumber)/10000 ,'month5_Amount'=(month5_Amount * r.rateNumber)/10000 ,'month6_Amount'=(month6_Amount * r.rateNumber)/10000 ,'20090706'=(select (weeklyAmount * r.rateNumber)/10000 from accountWeekly aw  where weeklyDate = '20090706' and aw.accountId = v.accountId ) ,'20090713'=(select (weeklyAmount * r.rateNumber)/10000 from accountWeekly aw  where weeklyDate = '20090713' and aw.accountId = v.accountId )  into #t from viewDisplayInfo v,rate r where  bibie = r.rateId and inout = 1 and ZHID = 1 order by WDName desc” 
//sqlParam这个语句是自动生成的，要传到存储过程中 
cmd1 = con.prepareCall("{call execSql_proc(?)}"); 
cmd1.setString(1, sqlParam); 
cmd1.execute(); 报错如下：java.sql.SQLException: 对象名 '#t' 无效。 
java.lang.NumberFormatException: For input string: "null" --存储过程是个判断执行那条语句 
create proc execSql_proc 
  @sql varchar(5000) 
as begin 
  IF EXISTS (SELECT * FROM tempdb.#t ) 
    begin 
      drop table tempdb.#t  
      exec @sql 
    end 
  else 
    exec @sql  
end 
直接在sql里面执行 exec execSql_proc  'select select accountName,WDName into #t from viewDisplayInfo v,rate r where  bibie = r.rateId and inout = 1 and ZHID = 1'会报错:对象名 '#t' 无效 上面的sqlParam 语句是用程序动态生成的，因为要通过sqlParam动态生成临时表,再在程序里调用临时表生成报表，是不是临时表的生命周期在存储过程结束后就终止了？那样的话再调用临时表生成报表也行不通了？若用##t的话，会不会产生并发操作问题？试过tempdb.#t和tempdb..#t都不行，好像里面单引号也有冲突肯请高手帮忙解决

Answer 1

create proc execSql_proc 
  @sql varchar(5000) 
as begin 
  IF EXISTS (SELECT * FROM tempdb.#t ) 
        drop table tempdb.#t  
   go
      exec @sql 
end 
试试

Answer 2

to:feixianxxx
代码有语法错误：
第 7 行: '#t' 附近有语法错误。
服务器: 消息 137，级别 15，状态 2，行 1
必须声明变量 '@sql'。----------------------------
用这个存储过程
create proc execSql_proc 
  @sql varchar(5000) 
as begin 
  IF EXISTS (SELECT * FROM tempdb.#t ) 
    begin 
      drop table tempdb.#t  
      exec @sql 
    end 
  else 
    exec @sql  
end 
单独执行：select accountName,WDName into  #t   from viewDisplayInfo v,rate r 
where  bibie = r.rateId and inout = 1 and ZHID = 1 可以成功
如果exec execSql_proc   'select accountName,WDName into  #t   from viewDisplayInfo v,rate r where  bibie = r.rateId and inout = 1 and ZHID = 1' 这个就不行，报错：对象名 '#t' 无效。

Answer 3

用全局临时表##t，
exec execSql_proc   'select accountName,WDName into  ##t   from viewDisplayInfo v,rate r where  bibie = r.rateId and inout = 1 and ZHID = 1' 
也提示：对象名 '##t' 无效。
单独执行select accountName,WDName into  ##t   from viewDisplayInfo v,rate r where  bibie = r.rateId and inout = 1 and ZHID = 1就可以，但需要存储过程判断临时表是否存在

Answer 4

create proc execSql_proc
   @sql varchar(500)
asbegin
   IF  EXISTS (SELECT * FROM #t) 
    begin
       drop table #t
       exec @sql
    end
   else
      exec @sql
    
end exec execSql_proc   'select accountName,WDName into  #t   from viewDisplayInfo v,rate r where  bibie = r.rateId and inout = 1 and ZHID = 1' 

Answer 5

create proc execSql_proc
   @sql varchar(500)
asbegin
   IF  EXISTS (SELECT * FROM #t) 
        drop table #t
       go
       create table #t(accountName varchar(100),WDName varchar(100)) 
       insert into #t 
       exec @sql
 
    
end exec execSql_proc   'select accountName,WDName   from viewDisplayInfo v,rate r bibie = r.rateId and inout = 1 and ZHID = 1' 

Answer 6

create proc execSql_proc
   @sql varchar(500)
asbegin
   IF  object_id('#t') is not null 
        drop table #t
       go
       create table #t(accountName varchar(100),WDName varchar(100)) 
       go
       insert into #t 
       exec @sql
   end exec execSql_proc 'select accountName,WDName from viewDisplayInfo v,rate r bibie = r.rateId and inout = 1 and ZHID = 1' 
临时表在内部建立 这样 应该可以

Answer 7

改成实际表执行exec execSql_proc  'insert into  test select  accountName,WDName  from viewDisplayInfo v,rate r where  bibie = r.rateId and inout = 1 and ZHID = 1' 报错：服务器: 消息 2812，级别 16，状态 62，行 12
未能找到存储过程 'insert into  test select  accountName,WDName  from viewDisplayInfo v,rate r 
where  bibie = r.rateId and inout = 1 and ZHID = 1'。存储过程是有的，我已经创建执行了，奇怪

Answer 8

回：feixianxxx存储过程里面不能用go关键字吧，报错：
第 7 行: '#t' 附近有语法错误。
服务器: 消息 2714，级别 16，状态 6，行 1
数据库中已存在名为 '#t' 的对象。
服务器: 消息 137，级别 15，状态 2，行 2
必须声明变量 '@sql'。执行这调语句：
exec createTemp_proc 'select accountName,WDName from viewDisplayInfo v,rate r bibie = r.rateId and inout = 1 and ZHID = 1' 
还是会报错：未能找到存储过程 'select accountName,WDName from viewDisplayInfo v,rate r bibie = r.rateId and inout = 1 and ZHID = 1'。怎么会这样呢？

Answer 9

exec @sql -----> exec(@sql)

Answer 10

你先按照9楼写的试试
如果不行 把九楼里的临时表 换成实际表 注意我在9楼写
 'select accountName,WDName from viewDisplayInfo v,rate r bibie = r.rateId and inout = 1 and ZHID = 1' 
into已经没了。。

Answer 11

alter proc execSql_proc
   @sql varchar(500)
asbegin
   IF  object_id('tempdb..##t') is not null
      drop table tempdb..##t
   exec (@sql) 
end 
goexec execSql_proc '
select accountName,WDName 
into  ##t   
from viewDisplayInfo v,rate r 
where  bibie = r.rateId 
and inout = 1 
and ZHID = 1' 

Answer 12

这样才行：
drop proc createTemp_proc
go
create  proc createTemp_proc
   @sql varchar(500)
asbegin
   IF  object_id('tempdb.dbo.##t') is not null
      drop table tempdb.##t
   exec (@sql) 
end exec createTemp_proc 
'select accountName,WDName ,
(month0_Amount * r.rateNumber)/10000  month0_Amount,(month1_Amount * r.rateNumber)/10000  month1_Amount,
(month2_Amount * r.rateNumber)/10000  month2_Amount,(month3_Amount * r.rateNumber)/10000  month3_Amount,
(month4_Amount * r.rateNumber)/10000  month4_Amount,(month5_Amount * r.rateNumber)/10000  month5_Amount,
(month6_Amount * r.rateNumber)/10000  month6_Amount,
(select (weeklyAmount * r.rateNumber)/10000  from accountWeekly aw  where weeklyDate = ''20090706'' and aw.accountId = v.accountId )  ''20090706_name''  ,
(select (weeklyAmount * r.rateNumber)/10000  from accountWeekly aw  where weeklyDate = ''20090713'' and aw.accountId = v.accountId )  ''20090713_name''
   into ##t 
 from viewDisplayInfo v,rate r 
where  bibie = r.rateId and inout = 1 and ZHID = 1 ' 
go
执行存储过程时报错：
第 6 行: 'a' 附近有语法错误。请问这个单引号该怎么弄，20090706_name是自定义字段名，前面的20090706是参数值##t全局临时表，会不会在并发操作中出现问题？

Answer 13

--在SQL 变量里，两个''代表1个'
--try:
declare @str varchar(100),@str1 varchar(100),@sql varchar(8000)
--先建全局临时表，再插入。
create table ##tb(.............)
set @sql='select accountName,WDName ,
(month0_Amount * r.rateNumber)/10000  month0_Amount,(month1_Amount * r.rateNumber)/10000  month1_Amount,
(month2_Amount * r.rateNumber)/10000  month2_Amount,(month3_Amount * r.rateNumber)/10000  month3_Amount,
(month4_Amount * r.rateNumber)/10000  month4_Amount,(month5_Amount * r.rateNumber)/10000  month5_Amount,
(month6_Amount * r.rateNumber)/10000  month6_Amount,
(select (weeklyAmount * r.rateNumber)/10000  from accountWeekly aw  where weeklyDate = ''' + @str + ''' and aw.accountId = v.accountId )  ['+@str+'_name],
(select (weeklyAmount * r.rateNumber)/10000  from accountWeekly aw  where weeklyDate = ''' + @str1 + ''' and aw.accountId = v.accountId ) ['+@str1+'_name]
 from viewDisplayInfo v,rate r 
where  bibie = r.rateId and inout = 1 and ZHID = 1'
insert ##tb exec createTemp_proc @sql

Answer 14

因为临时表的字段是不固定的，sql参数语句是变化的，所以不能固定用create table ##t。。exec createTemp_proc 
'select accountName,WDName ,
(month0_Amount * r.rateNumber)/10000  month0_Amount,(month1_Amount * r.rateNumber)/10000  month1_Amount,
(month2_Amount * r.rateNumber)/10000  month2_Amount,(month3_Amount * r.rateNumber)/10000  month3_Amount,
(month4_Amount * r.rateNumber)/10000  month4_Amount,(month5_Amount * r.rateNumber)/10000  month5_Amount,
(month6_Amount * r.rateNumber)/10000  month6_Amount,
(select (weeklyAmount * r.rateNumber)/10000  from accountWeekly aw  where weeklyDate = ''20090706'' and aw.accountId = v.accountId )  ''20090706_name''  ,
(select (weeklyAmount * r.rateNumber)/10000  from accountWeekly aw  where weeklyDate = ''20090713'' and aw.accountId = v.accountId )  ''20090713_name''
   into ##t 
 from viewDisplayInfo v,rate r 
where  bibie = r.rateId and inout = 1 and ZHID = 1 ' 日期的那个单引号我用两个'和三个'都试了，不行

Answer 15

把这段长语句放到一个varchar(8000)变量里，print 出来看看就知道了。

Answer 16

--try:
exec createTemp_proc 
'select accountName,WDName , 
(month0_Amount * r.rateNumber)/10000  month0_Amount,(month1_Amount * r.rateNumber)/10000  month1_Amount, 
(month2_Amount * r.rateNumber)/10000  month2_Amount,(month3_Amount * r.rateNumber)/10000  month3_Amount, 
(month4_Amount * r.rateNumber)/10000  month4_Amount,(month5_Amount * r.rateNumber)/10000  month5_Amount, 
(month6_Amount * r.rateNumber)/10000  month6_Amount, 
(select (weeklyAmount * r.rateNumber)/10000  from accountWeekly aw  where weeklyDate = ''20090706'' and aw.accountId = v.accountId )  [20090706_name]  , 
(select (weeklyAmount * r.rateNumber)/10000  from accountWeekly aw  where weeklyDate = ''20090713'' and aw.accountId = v.accountId )  [20090713_name] 
  into ##t 
from viewDisplayInfo v,rate r 
where  bibie = r.rateId and inout = 1 and ZHID = 1' 

Answer 17

在SQL变量里，一般 常量两边各2个'',字符变量两边是3个'''，因为还要和+连接，用去一个。

Answer 18

exec createTemp_proc 'select accountName,WDName ,
(month0_Amount * r.rateNumber)/10000  month0_Amount,(month1_Amount * r.rateNumber)/10000  month1_Amount,
(month2_Amount * r.rateNumber)/10000  month2_Amount,(month3_Amount * r.rateNumber)/10000  month3_Amount,
(month4_Amount * r.rateNumber)/10000  month4_Amount,(month5_Amount * r.rateNumber)/10000  month5_Amount,
(month6_Amount * r.rateNumber)/10000  month6_Amount,
(select (weeklyAmount * r.rateNumber)/10000  from accountWeekly aw  where weeklyDate = ''20090706'' and aw.accountId = v.accountId )  ''20090706_name'' ,
(select (weeklyAmount * r.rateNumber)/10000  from accountWeekly aw  where weeklyDate = ''20090713'' and aw.accountId = v.accountId )  ''20090713_name''
   into ##t 
 from viewDisplayInfo v,rate r 
where  bibie = r.rateId and inout = 1 and ZHID = 1 '
我用print试了，用两个单引号就ok，但报错误：第 6 行: 'a' 附近有语法错误。
这里都没有a

Answer 19

exec createTemp_proc 'select accountName,WDName,
(month6_Amount * r.rateNumber)/10000  month6_Amount,
(select (weeklyAmount * r.rateNumber)/10000  from accountWeekly  aw  where weeklyDate = ''20090706''  and  aw.accountId = v.accountId )  ''20090706_name'' ,
(select (weeklyAmount * r.rateNumber)/10000  from accountWeekly  aw  where weeklyDate = ''20090713''  and  aw.accountId = v.accountId )  ''20090713_name''
   into ##t 
 from viewDisplayInfo v,rate r 
 where  bibie = r.rateId and inout = 1 and ZHID = 1 '
我把代码截了一段，就可以成功了，是不是语句太长了？

Answer 20

你的那个参数定义的小了，改为varchar(8000)
这样应该就没问题了。
create proc execSql_proc 
  @sql varchar(8000) 
as 

Answer 21

如果是SQL 2005的话，定义为varchar(max)

Answer 22

上面问题已解决，谢谢各位！！！不过在java执行过程中又出问题，代码如下：try
{  
System.out.println("sqlParam = "+ sqlParam); 
    con = DBConnection.getConnection();//获取数据库连接
     cmd1 = con.prepareCall("{call createTemp_proc(?)}");
    cmd1.setString(1, sqlParam);
    cmd1.execute() cmd2 = con.prepareCall("{call insert_tempTable_proc}");
cmd2.execute()
            info = BaseDao.split(t_Param, pageNo, 50); //查询数据库，返回的显示值
     info.insertElementAt((Vector)map.get("allListValue_table"), 0);
    total = BaseDao.getPageT();
}
catch(Exception e)
{          
System.out.println("error...");
e.printStackTrace();
}下面输出的sqlParam在SQL查询分析器里面执行时可以成功的，这里还是单引号的问题，可我试了用一个，两个，三个，四个单引号都不行，不知道怎么搞！sqlParam =  select accountName,WDName ,(month0_Amount * r.rateNumber)/10000  month0_Amount,(month1_Amount * r.rateNumber)/10000  month1_Amount,(month2_Amount * r.rateNumber)/10000  month2_Amount,(month3_Amount * r.rateNumber)/10000  month3_Amount,(month4_Amount * r.rateNumber)/10000  month4_Amount,(month5_Amount * r.rateNumber)/10000  month5_Amount,(month6_Amount * r.rateNumber)/10000  month6_Amount,(select (weeklyAmount * r.rateNumber)/10000  from accountWeekly aw  where weeklyDate = ''20090706'' and aw.accountId = v.accountId )  ''20090706_name''   ,(select (weeklyAmount * r.rateNumber)/10000  from accountWeekly aw  where weeklyDate = ''20090713'' and aw.accountId = v.accountId )  ''20090713_name''     into ##t from viewDisplayInfo v,rate r where  bibie = r.rateId and inout = 1 and ZHID = 1 
java.sql.SQLException: 第 1 行: '20090706' 附近有语法错误。
at net.sourceforge.jtds.jdbc.SQLDiagnostic.addDiagnostic(SQLDiagnostic.java:368)
at net.sourceforge.jtds.jdbc.TdsCore.tdsErrorToken(TdsCore.java:2816)
at net.sourceforge.jtds.jdbc.TdsCore.nextToken(TdsCore.java:2254)
at net.sourceforge.jtds.jdbc.TdsCore.getMoreResults(TdsCore.java:636)
at net.sourceforge.jtds.jdbc.JtdsStatement.processResults(JtdsStatement.java:584)
at net.sourceforge.jtds.jdbc.JtdsStatement.executeSQL(JtdsStatement.java:546)
at net.sourceforge.jtds.jdbc.JtdsPreparedStatement.execute(JtdsPreparedStatement.java:562)
at org.apache.tomcat.dbcp.dbcp.DelegatingPreparedStatement.execute(DelegatingPreparedStatement.java:169)
at com.test.action.InOutAmountAction.execute(InOutAmountAction.java:191)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:597)

Answer 23

所有问题都已解决，非常感谢各位！总结几点：
drop proc createTemp_proc
go
create  proc createTemp_proc
   @sql varchar(500)
asbegin
   IF  object_id('tempdb.dbo.##t') is not null
      drop table tempdb.##t
   exec (@sql) 
end 
判断临时表是否存在，一定要用：IF  object_id('tempdb.dbo.##t') is not null，而不是tempdb.##t
局部临时表的生命周期是短暂的，只存在于存储过程，如果在存储过程还要调用，必须用全局临时表在sql里面两个单引号当一个单引号，如果是程序传递参数进sql，按照程序格式就可以，只要一个单引号

Answer 24

试一下,
create proc execSql_proc 
  @sql narchar(8000) 
as 
  ....
 
 

Answer 25

对象名 '#t' 无效:drop table #t后,需要创建临时表#t.

Answer 26

alter proc execSql_proc
   @sql varchar(500)
asbegin
   IF  object_id('tempdb..##t') is not null
      drop table tempdb..##t
   exec (@sql) 
end 
goexec execSql_proc '
select accountName,WDName 
into  ##t   
from viewDisplayInfo v,rate r 
where  bibie = r.rateId 
    and inout = 1 
    and ZHID = 1' 

Answer 27

这位朋友的办法应该可行
楼主，你的问题在于，你只是在存储过程中执行sql，当在执行之前，是没有#t这么个临时表的，所以你
select * from #t的话系统会认为你执行了不存在的表。就出错了！

Answer 28

alter proc execSql_proc
   @sql varchar(500)
asbegin
   IF  EXISTS (SELECT * FROM #t) 
    begin
       drop table #t
       exec (@sql)
    end
   else
      exec (@sql)
    
end 
gocreate table #t(a int)
exec execSql_proc   'select accountName,WDName into  #t   from viewDisplayInfo v,rate r where  bibie = r.rateId and inout = 1 and ZHID = 1'