Using double-quotes in a SQLCMD query

Question

I'd like to issue the following command through SQLCMD:

IF DB_ID('My_DB') IS NOT NULL DROP DATABASE My_DB

There are a few caveats though...

1. The database name is dynamic
2. The program which is running the command (BuildForge) will not replace a variable if it appears within single quotes

So, I can't use:

-Q"IF DB_ID('${db_name}') IS NOT NULL DROP DATABASE ${db_name}

because it will use the literal ${db_name} in the single-quotes. I also can't use:

-Q"IF DB_ID("${db_name}") IS NOT NULL DROP DATABASE ${db_name}

because SQLCMD gets tripped up on the begin and end of the double quotes.

I know that I can create a script and pass in a parameter for the database name, but is there any way to do this in a single command line without a script? Any way to escape the double-quotes, etc.? Is there a BuildForge syntax that would let me build the string correctly?

Thanks!

Answer 1

I'd normally use -q and a script to do this but some thoughts...

Can you do this?

-Q"IF DB_ID(RTRIM(LTRIM('    ${db_name}    '))) IS NOT NULL DROP DATABASE ${db_name}"

Or this?

-Q"BEGIN TRY DROP DATABASE ${db_name} END TRY BEGIN CATCH IF ERROR_NUMBER() <> 3701 RAISERROR('some error not related to db not there', 16, 1) END CATCH"