Использовать базу данных внутри хранимой процедуры

Мне нужно сделать хранимую процедуру, которая создает пользователя в более чем одной базе данных. Примерно так:

USE [database1]

CREATE USER [userLogin] FOR LOGIN [userLogin]

USE [database2]

CREATE USER [userLogin] FOR LOGIN [userLogin]

Поскольку оператор CREATE USER выполняет свою работу в текущей базе данных, мне нужно использовать оператор USE для переключения между базами данных, но его нельзя использовать внутри хранимых процедур.

Как я могу это сделать?

Ответ 1

Динамический SQL

CREATE PROCEDURE spTestProc
AS

EXEC ('USE [database1]; CREATE USER [userLogin] FOR LOGIN [userLogin]')

EXEC ('USE [database2]; CREATE USER [userLogin] FOR LOGIN [userLogin]')
GO

Ответ 2

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

CREATE PROCEDURE usp_CreateTwoUSers

AS
BEGIN

    -- Create a user for a login in the current DB:
    Exec sp_grantdbaccess [userLogin], [name_in_db];

    -- Create a user for a login in an external DB:
    Exec ExternalDatabaseName.sys.sp_grantdbaccess [userLogin], [name_in_db];

END

Ответ 3

CREATE PROCEDURE spTestProc
AS
BEGIN

EXECUTE sp_executesql N'USE DB1 SELECT * FROM TABLE1'


EXECUTE sp_executesql N'USE DB2 SELECT * FROM Table2'


END

exec spTestProc

теперь он работает.

Ответ 4

Я сделал это, как показано ниже:

Alter Procedure testProc
@dbName varchar(50)
As
declare @var varchar(100)
set @var = 'Exec(''create table tableName(name varchar(50))'')'    
Exec('Use '+ @dbName + ';' + @var)
Exec testProc 'test_db'

Ответ 5

Следует отметить, что если вы хотите использовать одинарные кавычки в команде EXEC, вам нужно будет удвоить количество одинарных кавычек

например.

EXEC ('USE [database1]; select * from Authors where name = ''John'' ')

В этом примере у Джона есть две одинарные кавычки до и после него. Вы не можете использовать двойные кавычки для этого типа запросов.

Ответ 6

Если вы пишете динамический SQL с EXEC sp_executesql ('query1') или EXEC ('query2'), это вернет правильный дБ, который вы хотите. Если вы пишете статический SQL или ваш запрос вне динамических SQL-кавычек или паратезов, он будет работать на master (где вы создаете хранимую процедуру (по умолчанию это master)).

CREATE PROCEDURE master.dbo.mysp1
AS

    EXEC ('USE model; SELECT DB_NAME()') -- or sp_executesql N'USE model; SELECT DB_NAME()' 
    --this returns 'model'

GO


CREATE PROCEDURE master.dbo.mysp2
AS

    EXEC ('USE model;') -- or sp_executesql N'USE model;'
    SELECT DB_NAME() 
    -- this returns 'master'

GO

Ответ 7

Использование sp_executesql похоже на работу, для получения дополнительной информации см. http://msdn.microsoft.com/en-us/library/ms175170.aspx

Я тестировал его с помощью этого, и он работал нормально:

CREATE PROCEDURE spTestProc
AS
BEGIN

EXECUTE sp_executesql N'USE DB1;'

SELECT * FROM TABLE1
EXECUTE sp_executesql N'USE DB2;'

SELECT * FROM Table2

END

exec spTestProc