Dynamic SQL command not getting executed
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'
sql-server dynamic-sql sqlcommand asked Nov 19 '18 at 23:09
Kushan RandimaKushan Randima
63321237
add a comment |
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'
asked Nov 19 '18 at 23:09
Kushan RandimaKushan Randima
63321237
2
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.
– Sami
Nov 19 '18 at 23:21
add a comment |
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'
asked Nov 19 '18 at 23:09
Kushan RandimaKushan Randima
63321237
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'
asked Nov 19 '18 at 23:09
Kushan RandimaKushan Randima
63321237
asked Nov 19 '18 at 23:09
Kushan RandimaKushan Randima
63321237
edited Nov 20 '18 at 3:30
Kushan Randima
asked Nov 19 '18 at 23:09
Kushan RandimaKushan Randima
63321237
asked Nov 19 '18 at 23:09
Kushan RandimaKushan Randima
63321237
asked Nov 19 '18 at 23:09
Kushan RandimaKushan Randima
63321237
63321237
2
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.
– Sami
Nov 19 '18 at 23:21
add a comment |
2
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.
– Sami
Nov 19 '18 at 23:21
2
2
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.
– Sami
Nov 19 '18 at 23:21
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.
– Sami
Nov 19 '18 at 23:21
add a comment |
1 Answer
1
active
oldest
votes
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 what 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.
answered Nov 19 '18 at 23:13
Dale BurrellDale Burrell
3,14932351
1
Just to clarify, the problem in the example is that@tableNameis null. You are correct that the OP cannot declare params for the dynamic sql when constructed in this manner.
– DeanOC
Nov 19 '18 at 23:17
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53383951%2fdynamic-sql-command-not-getting-executed%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
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 what 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.
answered Nov 19 '18 at 23:13
Dale BurrellDale Burrell
3,14932351
1
Just to clarify, the problem in the example is that@tableNameis null. You are correct that the OP cannot declare params for the dynamic sql when constructed in this manner.
– DeanOC
Nov 19 '18 at 23:17
add a comment |
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 what 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.
answered Nov 19 '18 at 23:13
Dale BurrellDale Burrell
3,14932351
1
Just to clarify, the problem in the example is that@tableNameis null. You are correct that the OP cannot declare params for the dynamic sql when constructed in this manner.
– DeanOC
Nov 19 '18 at 23:17
add a comment |
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 what 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.
answered Nov 19 '18 at 23:13
Dale BurrellDale Burrell
3,14932351
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 what 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.
answered Nov 19 '18 at 23:13
Dale BurrellDale Burrell
3,14932351
edited Nov 19 '18 at 23:25
answered Nov 19 '18 at 23:13
Dale BurrellDale Burrell
3,14932351
answered Nov 19 '18 at 23:13
Dale BurrellDale Burrell
3,14932351
answered Nov 19 '18 at 23:13
Dale BurrellDale Burrell
3,14932351
3,14932351
1
Just to clarify, the problem in the example is that@tableNameis null. You are correct that the OP cannot declare params for the dynamic sql when constructed in this manner.
– DeanOC
Nov 19 '18 at 23:17
add a comment |
1
Just to clarify, the problem in the example is that@tableNameis null. You are correct that the OP cannot declare params for the dynamic sql when constructed in this manner.
– DeanOC
Nov 19 '18 at 23:17
1
1
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.– DeanOC
Nov 19 '18 at 23:17
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.– DeanOC
Nov 19 '18 at 23:17
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53383951%2fdynamic-sql-command-not-getting-executed%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
2
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.
– Sami
Nov 19 '18 at 23:21