Variable usage - 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

Variable usage

Author

Topic

Zoroaster
Aged Yak Warrior

702 Posts

Posted - 2007-08-30 : 12:12:41

Can anyone tell me why this won't work, and what the right way is to do it:

<code>
DECLARE @DBNAME
SET @DBAME = 'TESTDB'
ALTER DATABASE @DBNAME...
</code>

It doesn't like me using the variable there, I get:

Msg 102, Level 15, State 1, Line 3
Incorrect syntax near '@dbname'.

Is there a way around this?

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-08-30 : 12:24:52

You'd have to use dynamic SQL.

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

Kristen
Test

22859 Posts

Posted - 2007-08-30 : 12:25:25

ALTER DATABASE has to be the first statement in the block.

You would need to do something like:

DECLARE @DBNAME varchar(1000)
DECLARE @strSQL nvarchar(4000)
SET @DBAME = 'TESTDB'
SET @strSQL = 'ALTER DATABASE [' + @DBNAME + '] ...'
EXEC (@strSQL)

Kristen

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-08-30 : 12:35:42

and it is one of the bad practices to alter dbs on the fly

Madhivanan

Failing to plan is Planning to fail

Zoroaster
Aged Yak Warrior

702 Posts

Posted - 2007-08-30 : 12:54:31

Thanks Kristen!

Mahdivanan, I am just writing the proc for use with my own dev environment to save me some time making changes after installs.

-Z

Kristen
Test

22859 Posts

Posted - 2007-08-30 : 13:32:13

You could do

USE MyDatabase
GO
Alter Table and Column stuff ...

Kristen

Zoroaster
Aged Yak Warrior

702 Posts

Posted - 2007-08-30 : 21:22:44

quote:
Originally posted by Kristen

You could do

USE MyDatabase
GO
Alter Table and Column stuff ...

Kristen

Right, but I wanted it to be an input parm for the stored proc, or default to current. Your solution worked for that purpose. I was doing this mainly as a learning exercise more than anything and I definitely learned something.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-08-31 : 02:15:22

More on dsql
www.sommarskog.se/dynamic_sql.html

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources