在另一个数据库中创建存储过程 [英] Creating stored procedure in another database

问题描述

知道是否可以单独使用 T-SQL 在另一个数据库中创建一个过程，其中数据库的名称事先不知道并且必须从表中读取?有点像这个例子:

Any idea if it's possible to create a procedure in another database using T-SQL alone, where the name of the database is not known up front and has to be read from a table? Kind of like this example:

Use [MasterDatabase]
Declare @FirstDatabase nvarchar(100)
Select Top 1 @FirstDatabase=[ChildDatabase] From [ChildDatabases]
Declare @SQL nvarchar(4000)
Declare @CRLF nvarchar(10) Set @CRLF=nchar(13)+nchar(10)
Set @SQL =
    'Use [+'@Firstdatabase+']'+@CRLF+
    'Go'+@CRLF+
    'Create Proc [Test] As Select 123'
Exec (@SQL)

看到我想要做什么了吗?这个例子失败了，因为 Go 实际上不是 T-SQL 命令，而是查询分析器/SQL 管理工作室识别的东西，并产生错误.删除 Go 并且它也失败了，因为 Create Proc 必须是脚本的第一行.呜呜呜！！

See what I'm trying to do? This example fails because Go is actually not a T-SQL command but it something recognised by the query analyser/SQL management studio and produces an error. Remove the Go and it also fails because Create Proc must be the first line of the script. Arrgg!!

T-SQL 的语法不允许你做这样的事情:

The syntax of T-SQL doesn't allow you do things like this:

创建 [OtherDatabase].[dbo].[Test]

Create [OtherDatabase].[dbo].[Test]

这是一种耻辱，因为它会很好用！你可以用 Select 语句来做到这一点，可惜它不一致:

Which is a shame as it would work a treat! You can do that with Select statements, shame it's inconsistent:

从 [OtherDatabase]..[TheTable] 中选择 *

Select * From [OtherDatabase]..[TheTable]

干杯，罗布.

推荐答案

这很痛苦，但这就是我所做的.我从我在 sqlteam 上找到的一个例子中得到了这个，我想 - 你可能对我不加选择的 REPLACE 的引用方式有一些问题:

It's a pain, but this is what I do. I took this from an example I found on sqlteam, I think - you might have some quoting issues with the way I did the indiscriminate REPLACE:

DECLARE @sql AS varchar(MAX)
DECLARE @metasql as varchar(MAX)
DECLARE @PrintQuery AS bit
DECLARE @ExecQuery AS bit

SET @PrintQuery = 1
SET @ExecQuery = 0

SET @sql = 
'
CREATE PROCEDURE etc.
AS
BEGIN
END
'

SET @metasql = '
USE OtherDatabase
EXEC (''' + REPLACE(@sql, '''', '''''') + ''')
'

IF @PrintQuery = 1
    PRINT @metasql
IF @ExecQuery = 1
    EXEC (@metasql)

这篇关于在另一个数据库中创建存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！