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, Sep 02 '18 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, Sep 04 '18 at 04:55
No worries @MMiles. Always happy to help regardless. Glad your issues were resolved and thanks for the cleanup. — Francis, Sep 04 '18 at 09:20

Gordon Linoff · Accepted Answer · 2018-08-29T02:04:49.227

2

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 '18 at 02:04

answered Aug 28 '18 at 11:27

Gordon Linoff

1,242,037
58
646
786

1

Solved my problem. Thank you @GordonLinoff – M Miles Aug 28 '18 at 16:47
Sidenote in case anyone is trying this out and getting 'Procedure expects parameter @statement of type nvarchar: https://stackoverflow.com/a/2743920/4877765 Just define the sql statement as nvarchar before running – M Miles Aug 28 '18 at 17:05

score 1 · Answer 2 · answered Aug 28 '18 at 05:48

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 '18 at 05:48

Eric

242
1
2
7

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. – M Miles Aug 30 '18 at 05:15

Running a conditional sql script dependent on existence of database

2 Answers2