如何将数据库名称作为SP中的变量？

问题描述：

我们在这里专门使用存储过程，这引发了一些小问题。我们交叉引用两个不同的数据库，如dev中的dbdev..table1，qa中的dbqa..table1以及生产中的dbprod..table1。如何将数据库名称作为SP中的变量？

因此，每次我们部署到不同的环境时，我们必须搜索并从dbdev替换为dbqa或dbprod。

有没有办法使用同义词或任何SQL服务器机制来解决问题？

答

使用SQLCMD变量，这是由.SQL配置脚本的SQLCMD部署支持，以及VSDB项目。所以，你的供应脚本的样子：

create procedure usp_myProc 
as 
select .. from [$(crossdb)]..table1; 
go

当在生产中部署它运行sqlcmd /E /I provisoning.sql /v crossdb=dbprod，而QA的部署将通过sqlcmd /E /I provisioning.sql /v crossdb=dbqa来完成。见Using sqlcmd with Scripting Variables。

作为一个方面说明，我正在研究一个项目，该项目允许从.Net SqlClient（SqlConnection，SqlCommand）使用sqlcmd变量：dbutilsqlcmd项目。

SQLCMD变量必须在运行时指定，是否正确？目前不支持运行SQLCMD脚本，并在继续之前提示用户输入信息？我问，因为Oracle的SQLPlus已经支持了一段时间的功能（对我来说是9i）... – 2010-08-26 20:04:10

SQLCMD变量必须在启动命令中指定，如'/ v'参数，或者它们可以作为环境变量提供，或者它们可以是用'：setvar'在脚本中设置。没有提供变量值的交互方式。 – 2010-08-26 22:00:13

答

编号

无法为数据库创建同义词。不过，这是一个popular request。

是不是真的有必要重命名您的数据库为dbdev,dbqa, dbprod等？

它也被同一作者请求[这里]（http://connect.microsoft.com/SQLServer/feedback/details/288421/allow-create-synonym-for-database）。他必须*真的*想要这个功能。 :-) – 2010-08-26 18:15:26

对，数据库名称应该是一样的。它应该是不同的服务器。如果你的dev和qa与生产环境在同一个sql server中，你会错过很多好处。 – 2010-08-26 18:20:27

答

动态SQL

（原谅潜在的错别字，但概念是存在的）

Declare @dbname nvarchar(255), @sql nvarchar(max) 
set @dbname = 'db1' 

Set @sql = 'Select * From ' + @dbname + '.dbo.table1' 
exec sp_executesql @sql

看起来好像发现和替换是一个比这更好的选择，因为它很混乱，并会继续生产。此外，如果您尝试在用户定义的函数中使用它，则会遇到问题。 – 2010-08-26 18:18:53

答

SQL Server 2005支持的同义词，因此您可以创建synonym1指在开发环境到dbdev..table1，并在产品环境中使用dbprod..table1。你的SP（可能是视图）只是对同义词进行操作。

更新：

的最简单的方式创建同义词：

exec sys.sp_MSforeachtable 
    'print ''CREATE SYNONYM '' + REPLACE(''?'', ''].['', ''].[syn_'') + 
    '' FOR [my_database].? 
    GO'''

（有GO之前断行）

运行和粘贴结果存入新的查询窗口。

你知道吗有一种简单的方法来编写一个sql来动态地为每个对象定义一个同义词吗？ – Haoest 2010-08-26 23:37:28

答

您可以将数据库名称作为存储过程的参数，然后使用动态SQL构建查询。

例：

CREATE PROC MyStoredProcedure @DBName VARCHAR(50) 
AS 

    DECLARE @SQL VARCHAR(MAX) 
    SET @SQL = 'SELECT * FROM ' + @DBName + '.dbo.table1' 
    EXEC sp_executesql @SQL

，那么只需在调用存储过程与相应的DB名称：

EXEC MyStoredProcedure 'dbdev'

如何将数据库名称作为SP中的变量？

相关推荐