Solved

USE Statement with variable

Posted on 2007-11-22
2
711 Views
Last Modified: 2008-02-01
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.
0
Comment
Question by:joachimcarrein
2 Comments
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 20333501
> apparently the USE statement does not support variables.
correct.

>when i try like this:
>SET @SQL='USE ' + @DbAttachName;
>EXEC (@SQL);
>I get no error, but the database does not change...
 actually, it is changed, but only in the scope of that dynamic sql.
so:
>i suppose it only changes the database in the "EXEC" statement, and not in the script itself.
correct



only way:

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

Open in new window

0
 
LVL 4

Author Comment

by:joachimcarrein
ID: 20333604
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 :)
0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now