create proc execSql_proc
@sql varchar(500)
asbegin
IF EXISTS (SELECT * FROM #t)
begin
drop table #t
exec @sql
end
else
exec @sql
end select accountName,WDName ,
'month0_Amount'=(month0_Amount * r.rateNumber)/10000 ,
'month1_Amount'=(month1_Amount * r.rateNumber)/10000
into #t
from viewDisplayInfo v,rate r
where bibie = r.rateId
and inout = 1 and ZHID = 1
order by WDName desc在程序里面调用此存储过程,把上面这个语句传递过去,如果存在#t表就先删除,没有的话就直接执行java.sql.SQLException: 对象名 '#t' 无效。会出现上述错误,不知该如何修改。
@sql varchar(500)
asbegin
IF EXISTS (SELECT * FROM tempdb..#t )
begin
drop table tempdb..#t
exec @sql
end
else
exec @sql
end 是这样吗?
exec execSql_proc 'select accountName,WDName ,
'month0_Amount'=(month0_Amount * r.rateNumber)/10000 ,
'month1_Amount'=(month1_Amount * r.rateNumber)/10000
into #t
from viewDisplayInfo v,rate r
where bibie = r.rateId
and inout = 1 and ZHID = 1
order by WDName desc '在这个执行语句中,单引号冲突该怎么弄?请指教!谢谢
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"
[/code]'存储过程是个判断执行那条语句
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
望高手帮忙修改,谢谢!!!