PB代码:
ls_pdb_name = trim("pdb_bak")
declare proc_backup_pdb procedure for proc_backup_pdb :ls_pdb_name;
execute proc_backup_pdb;
if sqlca.sqlcode = 0 then
commit using sqlca;
Messagebox("提示", "备份执行成功!")
else
rollback;
Messagebox("警告", "备份执行失败!")
end if
SQL Server做的存储过程
CREATE procedure proc_backup_pdb @pd_sname varchar(36)
as
declare @de_sql nvarchar(200) ,
@cr_sql nvarchar(200)
begin
set @de_sql = 'DROP TABLE '+ @pd_sname
execute sp_executesql @de_sql
end
GO
这样在查询分析器里执行,可以执行
proc_backup_pdb 'pdb_bak'
GO
为什么执行PB的调用就失败?
------解决方案--------------------
可以，不过需要加一个临时表是否存在的判断，如下:
CREATE procedure proc_backup_pdb @pd_sname varchar(36)
as
declare @de_sql nvarchar(200) ,
@cr_sql nvarchar(200)
begin
set @de_sql = 'if object_id(''' + @pd_sname + ''') is not null DROP TABLE '+ @pd_sname
execute sp_executesql @de_sql
set @cr_sql = 'Select * into ' + @pd_sname + ' from jxc_2009.dbo.base_goods'
execute sp_executesql @cr_sql
select 1
end
GO