I need to convert some tables as Temporal (System versioned) ones. For this purpose, I have written an SQL command to be executed in a dynamic manner.
The query does not throw an error, but it doesn't execute the SQL command. It does not print the command using PRINT either.
I read the related articles in SO. According to them I have declared the variables in the script as well as providing them with sp_executesql. Please advise.
DECLARE @sqlCommand nvarchar(2000)
DECLARE @tableName nvarchar(100)
SET @sqlCommand =
'ALTER TABLE ' + @tableName + '
ADD [SysStartTime] DATETIME2
GO
ALTER TABLE ' + @tableName + '
ADD [SysEndTime] DATETIME2
GO
UPDATE ' + @tableName + ' SET [SysStartTime] = ''19000101 00:00:00.0000000'', [SysEndTime] = ''99991231 23:59:59.9999999''
GO
ALTER TABLE ' + @tableName + '
ALTER COLUMN [SysStartTime] DATETIME2 NOT NULL
GO
ALTER TABLE ' + @tableName + '
ALTER COLUMN [SysEndTime] DATETIME2 NOT NULL
GO
ALTER TABLE ' + @tableName + '
ADD PERIOD FOR SYSTEM_TIME ([SysStartTime], [SysEndTime])
ALTER TABLE ' + @tableName + ' SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = [History].ConditionAssessment))
GO'
PRINT @sqlCommand
EXECUTE sp_executesql @sqlCommand, N'@tableName nvarchar(100)', @tableName = 'ConditionAssessmentData'
It won't print nor execute because @tableName and @sqlCommand are both null and anything + null = null in T-SQL so they remain null.
In fact you can't do what you want anyway, because you can only use parameters in the same wsy that you can in a non-dynamic query, and that doesn't let you do things like alter table see http://www.sommarskog.se/dynamic_sql.html.
You would need to build the entire string including the table name and not pass any parameters into sp_executesql.
You would also want to consider carefully whether you want to provide such a powerful, and dangerous (because of the danger of SQL injection) procedure.