Running a conditional sql script dependent on existence of database

Question

I'm trying to automate a sql script to add a column to existing databases on a given system. The script will be run on a system with one database or a different one. The script should not cause error in any of the cases: 1. One of the two databases exists 2. Both databases exist 3. Neither database exists.

I tried this but I keep getting error when the database 'DatabaseName' does not exist. I want the script to be ignored in that case.

IF DB_ID('DatabaseName') IS NOT NULL
    BEGIN
    PRINT 'DatabaseName Exists'
    IF COL_LENGTH('[DatabaseName].[dbo].[Table]', 'NewColumn') IS NULL
        BEGIN
            ALTER TABLE [DatabaseName].[dbo].[Table]
            ADD [NewColumn] bit NOT NULL DEFAULT 0;
            PRINT 'Modified DatabaseName.Table'
        END
    END
ELSE
    BEGIN 
        PRINT 'DatabaseName Does Not Exist' 
        -- therefore do nothing
    END

This gives me the error:

Msg 2702, Level 16, State 2, Line 6
Database 'DatabaseName' does not exist.

I had also tried different variations of Use 'DatabaseName' with the same or similar errors because they are not existent.

To clarify: it is okay if it does not exist. I am just trying to handle the error gracefully so an installation continues

Is the Wix tag necessary, as I didn't see Wix mentioned in the original question? I was brought here thinking it was a Wix related issue. — Francis
– Francis, Commented Sep 2, 2018 at 23:21
@Francis, sorry to bring you here with undue cause. I am using this solution in conjunction to wix but I see your point. Removing the tag. — M Miles
– M Miles, Commented Sep 4, 2018 at 4:55
No worries @MMiles. Always happy to help regardless. Glad your issues were resolved and thanks for the cleanup. — Francis
– Francis, Commented Sep 4, 2018 at 9:20

Gordon Linoff · Accepted Answer · 2018-08-29 02:04:49Z

3

Use dynamic SQL. The problem is occurring during the compilation phase of the code. Dynamic SQL will "hide" the reference to the database from the initial compilation phase.

For instance, in SQL Server, this looks like:

IF COL_LENGTH('[DatabaseName].[dbo].[Table]', 'NewColumn') IS NULL
    BEGIN
        exec sp_executesql N'
ALTER TABLE [DatabaseName].[dbo].[Table]
    ADD [NewColumn] bit NOT NULL DEFAULT 0';
        PRINT 'Modified DatabaseName.Table'
    END

edited Aug 29, 2018 at 2:04

answered Aug 28, 2018 at 11:27

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

M Miles Over a year ago

Solved my problem. Thank you @GordonLinoff

M Miles Over a year ago

Sidenote in case anyone is trying this out and getting 'Procedure expects parameter @statement of type nvarchar: stackoverflow.com/a/2743920/4877765 Just define the sql statement as nvarchar before running

Eric · Accepted Answer · 2018-08-28 05:48:08Z

1

You can use a try catch block:

BEGIN TRY  
    -- Generate divide-by-zero error.  
    SELECT 1/0;  
END TRY  
BEGIN CATCH  
    -- Execute code if error
END CATCH;

answered Aug 28, 2018 at 5:48

Eric

2671 gold badge2 silver badges7 bronze badges

1 Comment

M Miles Over a year ago

While I like this solution for catching errors when something might go wrong, it did not solve my issue of my nonexistent database causing an error. The dynamic sql statement in @GordonLinoff 's answer solved that issue. Upvote for try/catch blocks in sql, which I was previously unaware of. Thank you.

Collectives™ on Stack Overflow

Running a conditional sql script dependent on existence of database

2 Answers 2

2 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related