Must declare the scalar variable ' ' error in SQL Server

I need to add a new column to the table with not null and default date time. But I get the error as below. I have checked multiple instances but the error occurs.

DECLARE @SQL NVARCHAR(1000), @date NVARCHAR(1000);
SET @date = '9999-12-31 00:00:00.000'

SET @SQL = 'ALTER TABLE [spi].[ProductClass]
                ADD lastUpdatedTime datetime NOT NULL
                    CONSTRAINT default_updateTime DEFAULT @date';

EXEC sp_executesql @SQL;

This is the error I get.

Must declare the scalar variable "@date".

Any solution?

Solution 1:

If you replace @date with the value, it'll work.

But let's look at why it isn't working. Your SQL:

DECLARE @SQL NVARCHAR(1000), @date NVARCHAR(1000);
SET @date = '9999-12-31 00:00:00.000'

SET @SQL = 'ALTER TABLE [spi].[ProductClass]
                ADD lastUpdatedTime datetime NOT NULL
                    CONSTRAINT default_updateTime DEFAULT @date';

EXEC sp_executesql @SQL;

Declares a couple of variables and executes a block of SQL that one of the variables represents. Your issue is that sp_executesql reads some text as SQL and tries to run it - if you can't run what you tell it to run, then it can't.

So if you were to crack open a new query window and attempt to run:

ALTER TABLE [spi].[ProductClass]
    ADD lastUpdatedTime datetime NOT NULL
        CONSTRAINT default_updateTime DEFAULT @date

It'd have a meltdown trying to figure out what @date is supposed to be.

DECLARE @date NVARCHAR(1000);

SET @date = '9999-12-31 00:00:00.000';

ALTER TABLE [spi].[ProductClass]
    ADD lastUpdatedTime datetime NOT NULL
        CONSTRAINT default_updateTime DEFAULT @date;

Would work, or simply:

ALTER TABLE [spi].[ProductClass]
    ADD lastUpdatedTime datetime NOT NULL
        CONSTRAINT default_updateTime DEFAULT '9999-12-31 00:00:00.000';

So, using concatenation you can do:

DECLARE @SQL NVARCHAR(1000), @date NVARCHAR(1000);
SET @date = '9999-12-31 00:00:00.000'
    
SET @SQL = 'ALTER TABLE [spi].[ProductClass]
                ADD lastUpdatedTime datetime NOT NULL
                    CONSTRAINT default_updateTime DEFAULT ' + @date;

EXEC sp_executesql @SQL;    

Which will get you a different error, so try including a pair of quotes in the date variable:

DECLARE @SQL NVARCHAR(1000), @date NVARCHAR(1000);
SET @date = '''9999-12-31 00:00:00.000'''
    
SET @SQL = 'ALTER TABLE [spi].[ProductClass]
                ADD lastUpdatedTime datetime NOT NULL
                    CONSTRAINT default_updateTime DEFAULT ' + @date;

EXEC sp_executesql @SQL;    

Solution 2:

What you are looking for is like below where you create a dynamic SQL via concatenation

DECLARE @SQL NVARCHAR(1000), @date NVARCHAR(1000);
SET @date = '9999-12-31 00:00:00.000'

SET @SQL = 'ALTER TABLE [spi].[ProductClass]
                ADD lastUpdatedTime datetime NOT NULL
                    CONSTRAINT default_updateTime DEFAULT '''+ @date+'''';

EXEC sp_executesql @SQL;