如何使用一个数据库表到另一个数据库的存储过程，动态传递数据库名称

Question

现在我通常使用一个数据库表到另一个数据库存储过程（数据库名称..表名称），但我动态传递数据库名称如何编写存储过程请给我任何

我正在写的 建议像这样

Select QT.Name,SC.Name as Status,QT.QuoteNumber,QT.PolicyNumber,LC.Name as LineCode,QT.DBAName as DBAName       
  from QMSV3Dev..Quote QT, QMSV3Dev..StatusCode SC, QMSV3Dev..LineCode LC      
  where QT.StatusCode = SC.StatusCode And QT.LineCode = LC.LineCode and       
  QT.DBAName like 'a%' order by QT.Name,QuoteNumber desc  

在上面的存储过程QMSV3Dev..Quote中，QMSV3Dev是数据库名称，Quote是表名称，这个sp在另一个数据库中执行，例如test now

相同的查询动态传递数据库名称

CREATE Procedure [dbo].[usp_GetSearch123]      
(      
 @SearchValue varchar(100),      
 @SearchBy varchar(250),
 @DbName varchar(50)      
)     
AS    
Begin
  Select QT.Name,SC.Name as Status,QT.QuoteNumber,QT.PolicyNumber,LC.Name as LineCode,QT.DBAName as DBAName         
  from  @DbName+'..Quote ' QT,@DbName+'.. StatusCode' SC, @DbName+'..Linecodes' LC      
  where QT.StatusCode = SC.StatusCode And QT.LineCode = LC.LineCode and       
  (QT.Name like @SearchValue+'%' or QT.DBAName like @SearchValue+'%')       
  order by QT.Name,QuoteNumber desc
 end 

Answer 1

答案比你想象的更简单。为此，您必须使用动态 SQL。

它会是这样的：

CREATE Procedure [dbo].[usp_GetSearch123]      
(      
 @SearchValue varchar(100),      
 @SearchBy varchar(250),
 @DbName varchar(50)      
)     
AS    
Begin

  Declare @cmd varchar(5000)

  select @cmd = 
  'Select QT.Name,SC.Name as Status,QT.QuoteNumber,QT.PolicyNumber,LC.Name as LineCode,QT.DBAName as DBAName         
  from ' + @DbName + '..Quote QT, ' + @DbName + '.. StatusCode SC, ' + @DbName+'..Linecodes LC      
  where QT.StatusCode = SC.StatusCode And QT.LineCode = LC.LineCode and       
  (QT.Name like "' + @SearchValue + '%" or QT.DBAName like "' + @SearchValue +'%")       
  order by QT.Name,QuoteNumber desc'

  exec(@cmd)
 end 

---

您创建一个 varchar 变量 @cmd 并将 select 放在它上面，然后通过 exec(@cmd)。