Declare and Use Variables - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Declare and Use Variables

Author

Topic

shapper
Constraint Violating Yak Guru

450 Posts

Posted - 2010-10-06 : 09:50:05

Hello,

I have the following:


use master
go

exec sp_configure 'filestream access level', 2;
reconfigure;
go 

declare @Database nvarchar(40) = 'MyDB';
declare @Login nvarchar(40) = 'MyDBLogin';
declare @Password nvarchar(40) = 'MyPass';
declare @Role nvarchar(40) = 'MyRole';

if exists (select * from sys.server_principals where name = @Database)
drop login @Login
go

I get the following error on "drop login @Login":

Incorrect syntax near '@Login'.

What am I doing wrong?

And why my variables should be after the filestream configuration?

If I have the variables declaration between "use master; go" and file stream configuration I get the error:

Must declare the scalar variable "@Database".

I would like to have global variables so I can use them everywhere on my script.

Thanks,
Miguel

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2010-10-06 : 10:08:25

You can use drop login @Login because SQL Server looks for the object named "@Login" not the contents of the variable @Login.

You need to use dynamic query in such cases:

exec('drop login ' + @Login + ')'

Secondly, you get syntax error when you declare variables between first two statements is because after the GO statement executed, the variables go out of scope as GO indicates end of batch.

Harsh Athalye
http://www.letsgeek.net/

shapper
Constraint Violating Yak Guru

450 Posts

Posted - 2010-10-06 : 10:22:48

quote:
Originally posted by harsh_athalye

Secondly, you get syntax error when you declare variables between first two statements is because after the GO statement executed, the variables go out of scope as GO indicates end of batch.

But can I declare global variables?

In this case I will create a database, logins, etc and not all can be in the same scope (I think).

So I would like to somehow use global variables?

Wouldn't be this a solution?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources