我用的是SQL 2000
下面代码没报错,但是不起作用,根本没有USE 到变量MYDBNAME的数据库,请教高手declare cur_mydbname cursor for select name from master..sysdatabases order by name open cur_mydbnamefetch next from cur_mydbname into @mydbname while @@fetch_status=0 begin
select @sqlstring=N'USE '+@mydbname
execute sp_executesql @sqlstring
--execute xp_use @sqlstring
下面代码没报错,但是不起作用,根本没有USE 到变量MYDBNAME的数据库,请教高手declare cur_mydbname cursor for select name from master..sysdatabases order by name open cur_mydbnamefetch next from cur_mydbname into @mydbname while @@fetch_status=0 begin
select @sqlstring=N'USE '+@mydbname
execute sp_executesql @sqlstring
--execute xp_use @sqlstring
declare @myprodno char(8),@myinvnum numeric(9,2),@mydepprice numeric(9,2)
declare @mypricedb numeric(9,2),@mycomjc char(2),@mydbname varchar(50)
declare @sqlstring nvarchar(40)if exists (select * from tempdb.dbo.sysobjects where id=object_id(N'tempdb..#tempdbname ') and type='u' )
drop table #tempdbname
if exists (select * from tempdb.dbo.sysobjects where id=object_id(N'tempdb..#tempinv ') and type='u' )
drop table #tempinv
set @begintime='2011-1-1'
set @endtime='2011-6-30'create table #tempinv ( prod_no char(8),inv_num numeric(9,2),dep_price numeric(9,2),prod_price_db numeric (9,2),com_jc char(2),my_db_name varchar(50) primary key (prod_no,com_jc,my_db_name))declare cur_mydbname cursor for select name from master..sysdatabases order by name open cur_mydbnamefetch next from cur_mydbname into @mydbname while @@fetch_status=0 begin
select @sqlstring=N'USE '+@mydbname
execute sp_executesql @sqlstring
--execute xp_use @sqlstring
print @mydbname if exists (select * from sysobjects where type='u' and name='inv_sub')
begin
if exists (select * from tempdb.dbo.sysobjects where id=object_id(N'tempdb..#temprec ') and type='u' )
drop table #temprec
select b.prod_no,sum(inv_num) as inv_num into #temprec from inv_sub b left join inv_main a on b.list_no=a.list_no where a.inv_date between @begintime and @endtime group by prod_no
declare cur_inv cursor for select prod_no,inv_num from #temprec
open cur_inv
fetch next from cur_inv into @myprodno,@myinvnum
while @@fetch_status=0
begin
select @mydepprice=dep_price,@mypricedb=prod_price_db from product where prod_no=@myprodno
select @mycomjc=com_jc from company
insert into #tempinv (prod_no,inv_num,dep_price,prod_price_db,com_jc,my_db_name) values ( @myprodno,@myinvnum,@mydepprice,@mypricedb,@mycomjc,@mydbname)
fetch next from cur_inv into @myprodno,@myinvnum
end
close cur_inv
deallocate cur_inv
end
fetch next from cur_mydbname into @mydbname
end
close cur_mydbname
deallocate cur_mydbname
select DB_NAME () '
请问这种用法的话 我这句怎样改?
if exists (select * from sysobjects where type='u' and name='inv_sub')
create table #(DBName sysname)
insert into # exec sp_msforeachdb 'if exists (select * from [?]..sysobjects where type=''u'' and name=''inv_sub'') select DB_Name()'select * from #
这个改写下面这句也相当困难,因为要跨数据库
select @mydepprice=dep_price,@mypricedb=prod_price_db from product where prod_no=@myprodno