Error When Exec Query

1

DECLARE @TableName NVARCHAR(150) = 'HR_Course'

DECLARE @SQL NVARCHAR(MAX)

SET @SQL = '
ALTER TABLE '+@TableName+'
ADD StartTime DATETIME2
'+ CHAR(13)+ CHAR(9)+'
GO
'+ CHAR(13) + CHAR(9)+'
ALTER TABLE '+@TableName+'
ADD EndTime DATETIME2
'

EXEC sys.sp_executesql @SQL
2

Don't use batch separator. The following should work.

As an aside, I don't usually like to do schema modifications such as this in a dynamic query. Usually I find that that is an indication of something about the design of the code that is not quite right.

DECLARE @TableName NVARCHAR(150) =  'HR_Course'

DECLARE @SQL NVARCHAR(MAX)

SET @SQL = '
ALTER TABLE '+@TableName+'
ADD StartTime DATETIME2
'+ CHAR(13)+ CHAR(9)+'
;
'+ CHAR(13) + CHAR(9)+'
ALTER TABLE '+@TableName+'
ADD EndTime DATETIME2'


EXEC sys.sp_executesql @SQL
3

thank you JamesK
the query display is part query
when exec on full query
the result is error
DECLARE @TableName NVARCHAR(150) = 'HR_BankType'

DECLARE @SQL NVARCHAR(MAX)

SET @SQL = '
ALTER TABLE '+@TableName+'
ADD StartTime DATETIME2
'+ CHAR(13) + CHAR(9)+'
;
'+ CHAR(13) + CHAR(9) +'
ALTER TABLE '+@TableName+'
ADD EndTime DATETIME2
'+ CHAR(13) + CHAR(9) +'
;
'+ CHAR(13) + CHAR(9) +'
UPDATE '+@TableName+' SET StartTime = ''19000101 00:00:00.0000000'', EndTime = ''99991231 23:59:59.9999999''
'+ CHAR(13) + CHAR(9) +'
;
'+ CHAR(13) + CHAR(9) +'
ALTER TABLE '+@TableName+'
ALTER COLUMN StartTime DATETIME2 NOT NULL
'+ CHAR(13) + CHAR(9) +'
;
'+ CHAR(13) + CHAR(9) +'
ALTER TABLE '+@TableName+'
ALTER COLUMN EndTime DATETIME2 NOT NULL
'+ CHAR(13) + CHAR(9) +'
;
'+ CHAR(13) + CHAR(9) +'
ALTER TABLE '+@TableName+'
ADD PERIOD FOR SYSTEM_TIME (StartTime, EndTime)
'+ CHAR(13) + CHAR(9) +'
;
'+ CHAR(13) + CHAR(9) +'
ALTER TABLE '+@TableName+'
SET(SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.'+@TableName+'_History, DATA_CONSISTENCY_CHECK = ON))'

EXEC sys.sp_executesql @SQL

4 
DECLARE @exec_sql bit = 0; /*set to 1 when ready to actually run the SQL*/
DECLARE @print_sql bit = 1;
DECLARE @SQL NVARCHAR(MAX)
DECLARE @TableName NVARCHAR(128) = 'HR_BankType'

SET @SQL = '
ALTER TABLE [' + @TableName + '] ADD
    StartTime DATETIME2 NOT NULL DEFAULT ''19000101'',
    EndTime DATETIME2 NOT NULL DEFAULT ''99991231 23:59:59.9999999''
'
IF @print_sql = 1
    PRINT @SQL
IF @exec_sql = 1
    EXEC(@SQL)

SET @SQL = '
ALTER TABLE [' + @TableName + '] ADD
    PERIOD FOR SYSTEM_TIME (StartTime, EndTime)
'
IF @print_sql = 1
    PRINT @SQL
IF @exec_sql = 1
    EXEC(@SQL)

SET @SQL = '
ALTER TABLE ['+ @TableName + '] SET(SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.['+@TableName+'_History], DATA_CONSISTENCY_CHECK = ON))
'
IF @print_sql = 1
    PRINT @SQL
IF @exec_sql = 1
    EXEC(@SQL)
5

thank you SCottPletcher
but I need to know what the problem in this query

6

You can't add a column to a table and try to reference that column in the same batch.
Good luck with this and future qs.