How to quote this dynamic SQL properly?

Question

I am trying to write some dynamic SQL like below:

EXECUTE master.sys.sp_msforeachdb 'USE [?]; EXEC (''

DECLARE @type NVARCHAR(32);

IF DB_NAME() IN (''master'',''model'',''msdb'',''tempdb'')
BEGIN
    SET @type = ''System'';
END

SELECT @type;

'')'

But I think the quoting is not working, as I get the error:

Msg 102, Level 15, State 1, Line 5 Incorrect syntax near 'master'.

Any tips on what is wrong?

Answer 1

You need to double escape everything inside EXEC(''), e.g.

IF DB_NAME() IN (''''master'''',''''model'''',''''msdb'''',''''tempdb'''')

The first escape is for sp_msforeachdb, as it stands currently (with just one) you end up with something like:

USE [Master];
EXEC ('IF DB_NAME() IN ('master', 'model', 'msdb', 'tempdb')
...

What you need is to end up with:'

USE [Master];
EXEC ('IF DB_NAME() IN (''master'', ''model'', ''msdb'', ''tempdb'')
...

So your full SQL becomes:

EXECUTE master.sys.sp_msforeachdb 'USE [?]; EXEC (''

DECLARE @type NVARCHAR(32);

IF DB_NAME() IN (''''master'''',''''model'''',''''msdb'''',''''tempdb'''')
BEGIN
    SET @type = ''''System'''';
END

SELECT @type;

'')'

However, it is not necessary to use EXEC at all:

EXECUTE master.sys.sp_msforeachdb 'USE [?]; 
    DECLARE @type NVARCHAR(32);

    IF DB_NAME() IN (''master'',''model'',''msdb'',''tempdb'')
    BEGIN
        SET @type = ''System'';
    END

    SELECT @type;'