Я могу установить схему по умолчанию для в рамках хранимой процедуры?

Помимо изменения самого @QueryText , единственное, что я могу придумать, это схема пользователя по умолчанию:

ALTER USER SO_Sept09_Reader WITH DEFAULT_SCHEMA = SO_Sept09

... а затем подключитесь как другой пользователь для каждой схемы, которую вы хотите использовать. Хакерский прием.

Но если ваш запрос все равно создается динамически (и я уверен, что вы знаете, почему это часто не лучшая идея), может быть проще всего просто добавить заполнитель схемы в текст запроса и передать схему имя вместе с запросом функции замены.

Неопробовано, но: не могли бы вы объединить данные разных схем в одно представление и добавить столбец, вызывающий имя схемы?

CREATE VIEW AllPosts AS
  SELECT Data1, Data2, 'Sept09' AS Partition FROM SO_Sept09..Posts
    UNION ALL
  SELECT Data1, Data2, 'Oct09' AS Partition FROM SO_Oct09..Posts
  ...

SELECT * FROM AllPosts WHERE Partition = 'Sept09'
SELECT * FROM dbo.AllPosts('Sept09') -- if use table-valued function instead

Другой возможностью является создание копий каждого SP в каждой схеме, неизмененное имя таблицы в SP относится к таблицам в той же схеме.

Обратите внимание, что это не так. не работает с динамическим SQL внутри такого SP:

CREATE PROCEDURE schema_a.SP
    @somesql AS varchar(MAX)
AS
BEGIN
    EXEC ( @somesql )
END

CREATE PROCEDURE schema_b.SP
    @somesql AS varchar(MAX)
AS
BEGIN
    EXEC ( @somesql )
END

Не будет работать, потому что сходство схемы потеряно внутри EXEC.

Хотя это:

CREATE PROCEDURE schema_a.SP
AS
BEGIN
    SELECT * FROM tbl -- Will use schema_a.tbl first
END

CREATE PROCEDURE schema_b.SP
AS
BEGIN
    SELECT * FROM tbl -- Will use schema_b.tbl first
END

работает нормально.

Аналогично:

EXEC ( 'EXEC schema_a.SP' )

будет очевидно, работает нормально.

Здесь есть части того, как это сделать в разных местах, но не все вместе. Способ сделать это:

1. Сделать уникального входа и пользователя для каждой схемы

2. Сделать этих пользователей владельцами каждой отдельной схемы.

3. Установить схему по умолчанию для каждого такого пользователя как схему, которой они владеют.

4. Используйте синтаксис EXECUTE ('sql commands') AS USER = 'schema-owner' для выполнения ваших команд SQL в контексте этой схемы по умолчанию.

Следующий сценарий демонстрирует это:

--====== 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'

Хорошо, у меня есть новый способ сделать это, который может работать для меня немного лучше. Это вариант моего комментария к ответу Майкла Петротта:

Но это дает мне идею, возможно, иметь несколько пользователей и выбирать строку подключения на лету.

Я буду иметь только одного пользователя для выполнения этих запросов, но я заменю строку подключения на лету, чтобы указать правильный исходный каталог.