Dynamic SQL command not getting executed

Question

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'

Cause both of your variables are Nulls, that's why it not print anything, and I suggest the you use Quotename() for tables, column... be carful for SQL Injection there. — Ilyes
– Ilyes, Commented Nov 19, 2018 at 23:21

Dale K · Accepted Answer · 2024-12-05 02:32:26Z

3

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 way 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.

edited Dec 5, 2024 at 2:32

answered Nov 19, 2018 at 23:13

Dale K

28.1k15 gold badges59 silver badges85 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

DeanOC Over a year ago

Just to clarify, the problem in the example is that @tableName is null. You are correct that the OP cannot declare params for the dynamic sql when constructed in this manner.

Collectives™ on Stack Overflow

Dynamic SQL command not getting executed

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related