存储过程中数据库名字做参数的使用

Question

如下,将一个数据库中的一个表的信息导入另一非本机的数据库脚本,中间导入过程未封装成存储过程的时候是能使用的.
CREATE PROCEDURE [spA8DC_BaseDatatoA8DC]
(
@database_1 VARCHAR(100), --数据源
@database_2 VARCHAR(100) --目标数据库
)
AS--会计科目导入insert into [@database_2].[Pub_Atype]
( [_id]
      ,[FullName]
      ,[UserCode]
      ,[ProfitSheetOrder]
      ,[BalanceSheetOrder]
      ,[Spell]
      ,[Flag]
      ,[ATypeID]
      ,[parid]
      ,[_Level]
      ,[soncount]
      ,[sonnum]
      ,[Comment]
      ,[AbbrName]
      ,[SysRow]
      ,[LBDirection]
      ,[CFDirection]
      ,[Nature]
      ,[IsOpen])
select [_id]
      ,[FullName]
      ,[UserCode]
      ,[ProfitSheetOrder]
      ,[BalanceSheetOrder]
      ,[Spell]
      ,[Flag]
      ,[ATypeID]
      ,[parid]
      ,[_Level]
      ,[soncount]
      ,[sonnum]
      ,[Comment]
      ,[AbbrName]
      ,[SysRow]
      ,[LBDirection]
      ,[CFDirection]
      ,[Nature]
      ,[IsOpen]
from srv_lnk.[@database_1].[Pub_Atype] a
where a.[SysRow]=0
Goexec spA8DC_BaseDatatoA8DC '[Database1].[dbo]','[database2].[dbo]'
--其中存储过程中间的 srv_lnk是远程连接.
exec   sp_addlinkedserver     'srv_lnk','','SQLOLEDB','192.168.0.18'
exec   sp_addlinkedsrvlogin   'srv_lnk','false',null,'sa','xxxxxx'
最后执行
exec spA8DC_BaseDatatoA8DC '[Database1].[dbo]','[database2].[dbo]'
这个的时候出错了说
对象名  'srv_lnk.@database_1.Pub_Atype' 无效。
不知道这里用参数的方式是否错了.请高手帮忙看看

Answer 1

exec('insert into ['+@database_2+'].[Pub_Atype]
...from srv_lnk.['+@database_1+'].[Pub_Atype] a
where a.[SysRow]=0')

Answer 2

用动态执行
exec(insert into ['+@database_2+'].[Pub_Atype]
    ( [_id]
      ,[FullName]
      ,[UserCode]
      ,[ProfitSheetOrder]
      ,[BalanceSheetOrder]
      ,[Spell]
      ,[Flag]
      ,[ATypeID]
      ,[parid]
      ,[_Level]
      ,[soncount]
      ,[sonnum]
      ,[Comment]
      ,[AbbrName]
      ,[SysRow]
      ,[LBDirection]
      ,[CFDirection]
      ,[Nature]
      ,[IsOpen])
select [_id]
      ,[FullName]
      ,[UserCode]
      ,[ProfitSheetOrder]
      ,[BalanceSheetOrder]
      ,[Spell]
      ,[Flag]
      ,[ATypeID]
      ,[parid]
      ,[_Level]
      ,[soncount]
      ,[sonnum]
      ,[Comment]
      ,[AbbrName]
      ,[SysRow]
      ,[LBDirection]
      ,[CFDirection]
      ,[Nature]
      ,[IsOpen]
from srv_lnk.['+@database_1+'].[Pub_Atype] a
where a.[SysRow]=0'
)

Answer 3

CREATE PROCEDURE [spA8DC_BaseDatatoA8DC]
(
    @database_1 VARCHAR(100),                --数据源
    @database_2 VARCHAR(100)                --目标数据库
)
AS--会计科目导入
DECLARE @SQLSTR NVARCHAR(4000)
SET @SQLSTR = 'insert into [@database_2].[Pub_Atype]
    ( [_id]
      ,[FullName]
      ,[UserCode]
      ,[ProfitSheetOrder]
      ,[BalanceSheetOrder]
      ,[Spell]
      ,[Flag]
      ,[ATypeID]
      ,[parid]
      ,[_Level]
      ,[soncount]
      ,[sonnum]
      ,[Comment]
      ,[AbbrName]
      ,[SysRow]
      ,[LBDirection]
      ,[CFDirection]
      ,[Nature]
      ,[IsOpen])
select [_id]
      ,[FullName]
      ,[UserCode]
      ,[ProfitSheetOrder]
      ,[BalanceSheetOrder]
      ,[Spell]
      ,[Flag]
      ,[ATypeID]
      ,[parid]
      ,[_Level]
      ,[soncount]
      ,[sonnum]
      ,[Comment]
      ,[AbbrName]
      ,[SysRow]
      ,[LBDirection]
      ,[CFDirection]
      ,[Nature]
      ,[IsOpen]
from srv_lnk.[@database_1].[Pub_Atype] a
where a.[SysRow]=0'exec(@SQLSTR)exec spA8DC_BaseDatatoA8DC '[Database1].[dbo]','[database2].[dbo]'
--其中存储过程中间的 srv_lnk是远程连接.
exec   sp_addlinkedserver     'srv_lnk','','SQLOLEDB','192.168.0.18'
exec   sp_addlinkedsrvlogin   'srv_lnk','false',null,'sa','xxxxxx'

Answer 4

CREATE PROCEDURE [spA8DC_BaseDatatoA8DC]
(
    @database_1 VARCHAR(100),                --数据源
    @database_2 VARCHAR(100)                --目标数据库
)
AS
--会计科目导入
exec('insert into '+@database_2+'.[Pub_Atype]
    ( [_id]
      ,[FullName]
      ,[UserCode]
      ,[ProfitSheetOrder]
      ,[BalanceSheetOrder]
      ,[Spell]
      ,[Flag]
      ,[ATypeID]
      ,[parid]
      ,[_Level]
      ,[soncount]
      ,[sonnum]
      ,[Comment]
      ,[AbbrName]
      ,[SysRow]
      ,[LBDirection]
      ,[CFDirection]
      ,[Nature]
      ,[IsOpen])
select [_id]
      ,[FullName]
      ,[UserCode]
      ,[ProfitSheetOrder]
      ,[BalanceSheetOrder]
      ,[Spell]
      ,[Flag]
      ,[ATypeID]
      ,[parid]
      ,[_Level]
      ,[soncount]
      ,[sonnum]
      ,[Comment]
      ,[AbbrName]
      ,[SysRow]
      ,[LBDirection]
      ,[CFDirection]
      ,[Nature]
      ,[IsOpen]
from srv_lnk.'+@database_1+'.[Pub_Atype] a
where a.[SysRow]=0
')

Answer 5

CREATE PROCEDURE [spA8DC_BaseDatatoA8DC]
(
    @database_1 VARCHAR(100),                --数据源
    @database_2 VARCHAR(100)                --目标数据库
)
AS--会计科目导入
DECLARE @SQLSTR NVARCHAR(4000)
SET @SQLSTR = 'insert into [@database_2].[Pub_Atype]
    ( [_id]
      ,[FullName]
      ,[UserCode]
      ,[ProfitSheetOrder]
      ,[BalanceSheetOrder]
      ,[Spell]
      ,[Flag]
      ,[ATypeID]
      ,[parid]
      ,[_Level]
      ,[soncount]
      ,[sonnum]
      ,[Comment]
      ,[AbbrName]
      ,[SysRow]
      ,[LBDirection]
      ,[CFDirection]
      ,[Nature]
      ,[IsOpen])
select [_id]
      ,[FullName]
      ,[UserCode]
      ,[ProfitSheetOrder]
      ,[BalanceSheetOrder]
      ,[Spell]
      ,[Flag]
      ,[ATypeID]
      ,[parid]
      ,[_Level]
      ,[soncount]
      ,[sonnum]
      ,[Comment]
      ,[AbbrName]
      ,[SysRow]
      ,[LBDirection]
      ,[CFDirection]
      ,[Nature]
      ,[IsOpen]
from srv_lnk.'+[@database_1]+'.[Pub_Atype] a
where a.[SysRow]=0'exec(@SQLSTR)exec spA8DC_BaseDatatoA8DC '[Database1].[dbo]','[database2].[dbo]'
--其中存储过程中间的 srv_lnk是远程连接.
exec   sp_addlinkedserver     'srv_lnk','','SQLOLEDB','192.168.0.18'
exec   sp_addlinkedsrvlogin   'srv_lnk','false',null,'sa','xxxxxx'