There has to be better code then this if statement

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

SQL Server 2000 Forums

SQL Server Development (2000)

There has to be better code then this if statement

Author

Topic

morphias0
Starting Member

20 Posts

Posted - 2006-02-10 : 10:13:24

Heres some background, I've been working on a script to backup all the databases on a server. While i dont mind typing out all the databases on the server I would like to have this code condensed as much as I can.

DECLARE @1DB varchar(100),
@2DB varchar(100),
@3DB varchar(100),
@4DB varchar(100),
@5DB varchar(100),
@6DB varchar(100),
@ServerName varchar(100),
@dbcount int,
@database varchar(100)

SET @ServerName = 'Wisepkg1'
set @1DB = 'Wise Services Database'
SET @2DB = 'Workbench Database'
SET @3DB = 'MASTER'
SET @4DB = 'MODEL'
SET @5DB = 'MSDB'
SET @6DB = 'Software Manager Database'
set @dbcount = 6

--Starting Loop
while @dbcount <> 0
begin

--Trying to compact from here on
--Setting up the databases names
IF @DBCOUNT = 1
BEGIN
SET @Database = @1DB
END
else IF @DBCOUNT = 2
BEGIN
SET @Database = @2DB
END
else IF @DBCOUNT = 3
BEGIN
SET @Database = @3DB
END
else IF @DBCOUNT = 4
BEGIN
SET @Database = @4DB
END
else IF @DBCOUNT = 5
BEGIN
SET @Database = @5DB
END
else IF @DBCOUNT = 6
BEGIN
SET @Database = @6DB
END

-----------------------------------------
Basically i want to have something like this:

set @database = '@' + cast(dbcount as varchar(100)) + 'DB'

----------------------------------------------------------

Having code like this would cut all the if statements and make it into one line. But it wont work, it will set @database to "@1DB" as a string and i want it to set it to @1DB the variable....IS THIS POSSIBLE? IS THERE A BETTER WAY?

Thanks for the help

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-02-10 : 10:18:20

Refer this
http://www.mindsdoor.net/SQLAdmin/BackupAllDatabases.html

Madhivanan

Failing to plan is Planning to fail

morphias0
Starting Member

20 Posts

Posted - 2006-02-10 : 10:49:57

If i were to use something like that i would have to run the stored procedure on all the servers, i would also have to create the admin db on all the servers as well, isnt that correct? The thing is, we hardly ever get new db on a server, if we decide to get a new db we will usually get a new server to have it run on. So i dont really mind typing in the db name for each server. I would really just like what i have to work instead of going through that. Is it possible for what i want.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-02-10 : 11:22:21

I don't think this is any better than what you have but...
There are a lot of ways to loop through a list of DBs. Here's one:


declare @database varchar(100)

select @database = 'master'

while @database is not null
begin
	--do you processing here
	print @database

	select @database = min(name) 
	from master..sysdatabases 
	where	name in 
		('Model'
		,'msdb'
		,'Workbench') --etc...
	and	name > @database
end

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources