我可以在存储过程中设置默认架构吗? [英] Can I set a default schema for within a stored procedure?
问题描述
我正在为 StackQL 进行下一次更新.
I'm working on the next update for StackQL.
我想做的一件事是能够查询多个版本.因此，例如，当我加载 10 月数据时，我并没有删除旧的 9 月数据库.它仍然在那里.事实上，您甚至可以通过包含这样的数据库名称来查询它:
One thing I want to do is have the ability to query over several releases. So when I loaded the October data, for example, I didn't delete the old September database. It's still out there. In fact, you can even still query it by including the database name like this:
select top 10 * from SO_Sept09..Posts
当他们开始为 ServerFault 和 SuperUser 提供数据时，这将变得更加重要.
This will be even more important as they start providing data for ServerFault and SuperUser.
但我不喜欢有一大堆数据库来支持这一点.我更愿意将所有数据放在同一个数据库中，并将每个不同的集合分开到它自己的模式中.但是为了使这成为可能，我需要能够根据传递给存储过程的参数将默认模式设置为运行查询的存储过程的一部分，该参数告诉它用户从未来的下拉列表中选择了哪个数据库出现在工具栏中.
But I don't like having a whole bunch of databases out there to support this. I'd much rather put all the data in the same database and separate each distinct set into it's own schema. But to make this possible, I need to be able to set a default schema as part of the stored procedure that runs the query, based on a parameter passed to the stored procedure that tells it which database the user selected from a future drop down list to appear in the tool bar.
StackQL 中的查询最终会像这样传递给 exec() 函数:
Queries at StackQL are eventually just passed to the exec() function like this:
exec(@QueryText)
我可以在存储过程中或在 QueryText 字符串 (ala USE [DatabaseName]) 的前面做些什么来设置查询中使用的默认架构?
Is there anything I can do either in the stored procedure or prepend to the QueryText string (ala USE [DatabaseName]) to set the default schema used in a query?
推荐答案
这里的各个地方都有如何做到这一点的部分，但不是全部.这样做的方法是:
There are pieces of how to do this in various places here, but not altogether. The way to do this is:
进行独特的登录每个架构的用户
Make a unique login & user for each schema
让这些用户成为每个不同架构的所有者.
Make these users the owners of each different schema.
将每个此类用户的默认架构设置为他们拥有的架构.
Set each such user's default schema to be the schema that they own.
使用语法 EXECUTE ('sql commands') AS USER = 'schema-owner' 在该默认模式的上下文中执行您的 SQL 命令.
Use the syntax EXECUTE ('sql commands') AS USER = 'schema-owner' to execute your SQL commands in the context of that default schema.
以下脚本演示了这一点:
The following script demonstrates this:
--====== Create the Login for the User:
CREATE LOGIN [UserTest1] WITH PASSWORD='whatever', DEFAULT_DATABASE=[TestUsers], DEFAULT_LANGUAGE=[us_english]
GO
--====== Make a User for the Login:
CREATE USER [UserTest1] FOR LOGIN [UserTest1]
GO
--====== Make a Schema owned by the User and default to it:
-- (I assume that you already have the schemas)
CREATE SCHEMA [UserTest1] AUTHORIZATION [UserTest1]
GO
ALTER USER [UserTest1] WITH DEFAULT_SCHEMA=[UserTest1]
GO
--====== Make a sProc in dbo
CREATE PROCEDURE [dbo].[TestSchema_Exec] AS
SELECT 'executing in schema [dbo]'
GO
--====== Make a similar sProc in New Schema
CREATE PROCEDURE [UserTest1].[TestSchema_Exec] AS
SELECT 'executing in schema [UserTest1]'
GO
--========= Demonstrate that we can switch Default Schemas:
EXEC('TestSchema_Exec')
EXEC('TestSchema_Exec') AS USER = 'UserTest1'
这篇关于我可以在存储过程中设置默认架构吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
