从存储过程中的不同模式中选择数据

Consider this:

考虑一下:

CREATE PROCEDURE [dbo].[setIdentifier](@oldIdentifierName as varchar(50), @newIdentifierName as varchar(50))
AS
BEGIN
    DECLARE @old_id as int;
    DECLARE @new_id as int;

    SET @old_id = (SELECT value FROM Configuration WHERE id = @oldIdentifierName);
    SET @new_id = (SELECT value FROM Configuration WHERE id = @newIdentifierName);

    IF @old_id IS NOT NULL AND @new_id IS NOT NULL
    BEGIN
        UPDATE Customer
        SET type = @new_id
        WHERE type = @old_id;
    END; 
END

[...]

[…]

EXECUTE dbo.setIdentifier '1', '2';

What this does is create a stored procedure that accepts two parameters which it then uses to update a Customer table.

它所做的是创建一个存储过程，该过程接受两个参数，然后用于更新客户表。

The problem is that the entire script above runs within a schema other than "dbo". Let's just assume the schema is "company1". And when the stored procedure is called, I get an error from the SELECT statement, which says that the Configuration table cannot be found. I'm guessing this is because MS SQL by default looks for tables within the same schema as the location of the stored procedure, and not within the calling context.

问题是，上面的整个脚本运行在一个模式中，而不是“dbo”。我们假设这个模式是“company1”。当调用存储过程时，我从SELECT语句中得到一个错误，该语句表示无法找到配置表。我猜这是因为MS SQL默认查找与存储过程的位置相同的模式中的表，而不是调用上下文中的表。

My question is this:

我的问题是:

Is there some option or parameter or switch of some kind that will tell MS SQL to look for tables in the "caller's default schema" and not within the schema that procedure itself is stored in?
是否有某种选项或参数或开关会告诉MS SQL在“调用者的默认模式”中查找表，而不是在存储过程本身的模式中?
If not, what would you recommend? I don't really want to prefix the tables with the schema name, because it would be kind of unflexible to do that. So I'm thinking about using dynamic sql (and the schema_name() function which returns the correct value even within the procedure), but I am just not experienced enough with MS SQL to construct the proper syntax.
如果没有，你会推荐什么?我不想在表前面加上模式名，因为这样做有点不灵活。因此，我正在考虑使用动态sql(以及schema_name()函数，该函数甚至在过程中也返回正确的值)，但我对MS sql的经验还不够，无法构建正确的语法。

2 个解决方案

#1

It would be a tad more efficient to explicitly specify the schema name. And generally speaking, schema's are mainly used to divide a database into logical area's. I would not anticipate on tables schema-hopping often.

显式地指定模式名会稍微有效一些。一般来说，模式主要用于将数据库划分为逻辑区域。我不会经常在桌子上跳来跳去的。

Regarding your question, you might want to have a look at the 'execute as' documentation on msdn, since it allows to explicitly control your execution context.

关于您的问题，您可能想看看msdn上的“作为执行”文档，因为它允许显式地控制您的执行上下文。

#2

I ended up passing the schema name to my script as a property on the command line for the "sqlcmd" command. Like this:

最后，我将模式名作为“sqlcmd”命令的命令行上的属性传递给脚本。是这样的:

C:/> sqlcmd -vSCHEMANAME=myschema -imysqlfile

In the SQL script I can then access this variable like this:

在SQL脚本中，我可以这样访问这个变量:

SELECT * from $(SCHEMANAME).myTable WHERE.... etc

Not quite as flexible as dynamic sql, but "good enough" as it were.

不像动态sql那样灵活，而是“足够好”。

Thanks all for taking time to respond.

谢谢大家花时间回复我。

#1