Can you conditionally determine database to apply script to

Question

Post reply

Can you conditionally determine database to apply script to

WayneS

SSC Guru

Points: 95460
More actions
February 11, 2010 at 1:17 pm

#138092

I'm not sure if this can be done. I'm trying to develop a method for applying scripts, where the database name is not consistent.
What I've tried is:
begin try
if exists (select 1 from sys.databases where name = 'dbname1') USE [dbname1] else
if exists (select 1 from sys.databases where name = 'dbname2') USE [dbname2] else
if exists (select 1 from sys.databases where name = 'dbname3') USE [dbname3]
else RaisError('Unsure what database to use', 11, 1)
end try
begin catch
declare @ErrorMessage varchar(500)
select @ErrorMessage = Error_Message()
RaisError(@ErrorMessage, 11, 1)
return
end catch
GO
-- add the commands to actually run here... i.e. CREATE PROCEDURE
print 'running commands'
the problems that I'm running into are:
1. If any of the specified database names don't exist, then the script won't compile with the error:
Msg 911, Level 16, State 1, Line 2
Could not locate entry in sysdatabases for database 'dbname1'. No entry found with that name. Make sure that the name is entered correctly.
2. Once the GO command is encountered, the remainder of the script will be run, even if an error was raised. The return properly immediately exits the current statement block, and proceeds to execute the next one. Since several DDL commands (i.e. CREATE PROCEDURE) must be the first statement of a block, a GO statement is necessary.
Desired results:
The first database in the list that exists on that server is used for the remainder of the script. If none of the databases specified exist, the script raises an error and does not run the remainder of the script (the entire script, not just the current statement block).
So, is there anyway to do this? (Too bad the USE command won't accept a variable...)
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

Only thing I can think of is to build the query dynamically and execut the query at the end of the try block.

SQLRNNR SSC Guru Points: 281334 More actions · Answer 2

You have to build it dynamically. Can't remember the cause or the reference but that first use statement will get executed every time.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

wschampheleer SSCertifiable Points: 5504 More actions · Answer 3

I agree with the suggestions made above.

The way I solve it goes like this:

DECLARE @sp_executesql NVARCHAR(150) = QUOTENAME('name of your database goes here') + '.sys.sp_executesql' ;

I put the statements I want to execute in @sql of type NVARCHAR(MAX).

To execute the statements on the database you specified earlier, run

EXEC @sp_executesql @sql ;

Regards,

Willem
http://wschampheleer.wordpress.com[/url]

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 4

Take a look at using SQLCMD mode instead - where you can then use a variable for the database name. You can review this article to see how you might approach this:

http://www.simple-talk.com/sql/sql-tools/the-sqlcmd-workbench/

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs