Link to home
Start Free TrialLog in
Avatar of Joachim Carrein
Joachim CarreinFlag for Belgium

asked on

USE Statement with variable

Hi,

i am trying to script the creation of a user and add it to a database like this:

declare @DbAttachName varchar(50);
SET @DbAttachName = 'test_script';
##USE @DbAttachName;##
CREATE USER [nvt] FOR LOGIN [nvt] WITH DEFAULT_SCHEMA=[dbo];
EXEC sp_addrolemember @rolename='db_owner',@membername='nvt';

the login was created succesfully, no problem there.
on the line with the ## there is an error. apparently the USE statement does not support variables.
when i try like this:
SET @SQL='USE ' + @DbAttachName;
EXEC (@SQL);
I get no error, but the database does not change...
i suppose it only changes the database in the "EXEC" statement, and not in the script itself.

How can i solve this?
The problem is, that the database is also attached in the same script (not when testing this)
so i can not fix the USE name.
ASKER CERTIFIED SOLUTION
Avatar of Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3]
Flag of Luxembourg image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Joachim Carrein

ASKER

that did the trick indeed. as i suspected the use is only in the scope. but i didn't think of executing those statements in the same scope :)