Sql-server – Whats wrong with the query

sql serversyntaxt-sql

I know that this works for a database called DB01_20161110:

ALTER AVAILABILITY GROUP AG1 ADD DATABASE DB01_20161110

I'm trying to add today's date automatically:

DECLARE @DateString   AS VARCHAR(256)
SET @DateString =     cast(year(getdate()) as varchar(4)) 
+     right('0' + cast(month(getdate()) as varchar(2)), 2)
+     right('0' + cast(day(getdate()) as varchar(2)), 2)
DECLARE @DB AS VARCHAR (40)
SET @DB = 'DB01_' + @DateString

ALTER AVAILABILITY GROUP AG1 ADD DATABASE @DB
GO

I get:

Msg 102, Level 15, State 1, Line 8
Incorrect syntax near '@DB'

Also tried:

DECLARE @DateString   AS VARCHAR(256)
SET @DateString =     cast(year(getdate()) as varchar(4)) 
+     right('0' + cast(month(getdate()) as varchar(2)), 2)
+     right('0' + cast(day(getdate()) as varchar(2)), 2)

ALTER AVAILABILITY GROUP AG1 ADD DATABASE 'DB01_' + @DateString
GO

Same error.

I use the same DECLARE part for other scripts so that's working.

Any advice?

Best Answer

You need to use dynamic SQL for this. The following example will work (as does a quick check to see that the database exists, you could add additional checks as well).

DECLARE @DateString AS VARCHAR(256) ,
    @ExecuteText NVARCHAR(4000);
SET @DateString = CAST(YEAR(GETDATE()) AS VARCHAR(4)) + RIGHT('0'
                                                              + CAST(MONTH(GETDATE()) AS VARCHAR(2)),
                                                              2) + RIGHT('0'
                                                              + CAST(DAY(GETDATE()) AS VARCHAR(2)),
                                                              2);
DECLARE @DB AS VARCHAR(40);
SET @DB = 'DB01_' + @DateString;


IF EXISTS ( SELECT  name
            FROM    sys.databases
            WHERE   name = @DB )
    BEGIN
        SET @ExecuteText = N'ALTER AVAILABILITY GROUP AG1 ADD DATABASE ' + @DB
            + ';';
        EXEC sys.sp_executesql @ExecuteText;
    END;
ELSE
    BEGIN
        RAISERROR(N'Database: %s does not exist', 16, 1, @DB);
    END;
GO
Related Question