Mixing
how to add new column if it's not exist
I want to write a procedure that adds a new column to a table if the column is not exists already. I wrote it for MySQL at first, and it worked successfully, but when I tried to write the same procedure for SQL SERVER, I got some syntax errors, also I am not sure if the SQL SERVER procedure is correct and will do the same job of the MySQL's one.
MySQL procedure:
SET @dbname = DATABASE();
SET @tablename = "my_table";
SET @columnname = "my_field";
SET @preparedStatement = (SELECT IF(
(
SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
WHERE
(table_name = @tablename)
AND (table_schema = @dbname)
AND (column_name = @columnname)
) > 0,
"SELECT 1",
CONCAT("ALTER TABLE ", @tablename, " ADD ", @columnname, " INT UNSIGNED NOT NULL;")
));
PREPARE alterIfNotExists FROM @preparedStatement;
EXECUTE alterIfNotExists;
DEALLOCATE PREPARE alterIfNotExists;
SQL SERVER procedure:
DECLARE @dbname varchar(128), @tablename varchar(11), @columnname varchar(11), @preparedStatement varchar(max);
SET @dbname = DB_NAME();
SET @tablename = "my_table";
SET @columnname = "my_field";
SET @preparedStatement = (SELECT CASE(
(
SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
WHERE
(table_name = @tablename)
AND (table_schema = @dbname)
AND (column_name = @columnname)
) > 0,
"SELECT 1",
CONCAT("ALTER TABLE ", @tablename, " ADD ", @columnname, " INT UNSIGNED NOT NULL;")
));
EXEC sp_prepare @preparedStatement;
I got a syntax error on line 12 for the GREATER THAN operator:
Msg 102, Level 15, State 1, Line 12 Incorrect syntax near '>'.
mysql
sql-server stored-procedures edited Nov 20 '18 at 9:50
Larnu
16.8k41630
asked Nov 20 '18 at 9:48
MohammadMohammad
1,85922556
add a comment |
I want to write a procedure that adds a new column to a table if the column is not exists already. I wrote it for MySQL at first, and it worked successfully, but when I tried to write the same procedure for SQL SERVER, I got some syntax errors, also I am not sure if the SQL SERVER procedure is correct and will do the same job of the MySQL's one.
MySQL procedure:
SET @dbname = DATABASE();
SET @tablename = "my_table";
SET @columnname = "my_field";
SET @preparedStatement = (SELECT IF(
(
SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
WHERE
(table_name = @tablename)
AND (table_schema = @dbname)
AND (column_name = @columnname)
) > 0,
"SELECT 1",
CONCAT("ALTER TABLE ", @tablename, " ADD ", @columnname, " INT UNSIGNED NOT NULL;")
));
PREPARE alterIfNotExists FROM @preparedStatement;
EXECUTE alterIfNotExists;
DEALLOCATE PREPARE alterIfNotExists;
SQL SERVER procedure:
DECLARE @dbname varchar(128), @tablename varchar(11), @columnname varchar(11), @preparedStatement varchar(max);
SET @dbname = DB_NAME();
SET @tablename = "my_table";
SET @columnname = "my_field";
SET @preparedStatement = (SELECT CASE(
(
SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
WHERE
(table_name = @tablename)
AND (table_schema = @dbname)
AND (column_name = @columnname)
) > 0,
"SELECT 1",
CONCAT("ALTER TABLE ", @tablename, " ADD ", @columnname, " INT UNSIGNED NOT NULL;")
));
EXEC sp_prepare @preparedStatement;
I got a syntax error on line 12 for the GREATER THAN operator:
Msg 102, Level 15, State 1, Line 12 Incorrect syntax near '>'.
mysql
sql-server stored-procedures edited Nov 20 '18 at 9:50
Larnu
16.8k41630
asked Nov 20 '18 at 9:48
MohammadMohammad
1,85922556
Which version of SQL server are you using?
– Stuart Grant
Nov 20 '18 at 9:52
@StuartGrant version 14
– Mohammad
Nov 20 '18 at 9:55
add a comment |
I want to write a procedure that adds a new column to a table if the column is not exists already. I wrote it for MySQL at first, and it worked successfully, but when I tried to write the same procedure for SQL SERVER, I got some syntax errors, also I am not sure if the SQL SERVER procedure is correct and will do the same job of the MySQL's one.
MySQL procedure:
SET @dbname = DATABASE();
SET @tablename = "my_table";
SET @columnname = "my_field";
SET @preparedStatement = (SELECT IF(
(
SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
WHERE
(table_name = @tablename)
AND (table_schema = @dbname)
AND (column_name = @columnname)
) > 0,
"SELECT 1",
CONCAT("ALTER TABLE ", @tablename, " ADD ", @columnname, " INT UNSIGNED NOT NULL;")
));
PREPARE alterIfNotExists FROM @preparedStatement;
EXECUTE alterIfNotExists;
DEALLOCATE PREPARE alterIfNotExists;
SQL SERVER procedure:
DECLARE @dbname varchar(128), @tablename varchar(11), @columnname varchar(11), @preparedStatement varchar(max);
SET @dbname = DB_NAME();
SET @tablename = "my_table";
SET @columnname = "my_field";
SET @preparedStatement = (SELECT CASE(
(
SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
WHERE
(table_name = @tablename)
AND (table_schema = @dbname)
AND (column_name = @columnname)
) > 0,
"SELECT 1",
CONCAT("ALTER TABLE ", @tablename, " ADD ", @columnname, " INT UNSIGNED NOT NULL;")
));
EXEC sp_prepare @preparedStatement;
I got a syntax error on line 12 for the GREATER THAN operator:
Msg 102, Level 15, State 1, Line 12 Incorrect syntax near '>'.
mysql
sql-server stored-procedures edited Nov 20 '18 at 9:50
Larnu
16.8k41630
asked Nov 20 '18 at 9:48
MohammadMohammad
1,85922556
I want to write a procedure that adds a new column to a table if the column is not exists already. I wrote it for MySQL at first, and it worked successfully, but when I tried to write the same procedure for SQL SERVER, I got some syntax errors, also I am not sure if the SQL SERVER procedure is correct and will do the same job of the MySQL's one.
MySQL procedure:
SET @dbname = DATABASE();
SET @tablename = "my_table";
SET @columnname = "my_field";
SET @preparedStatement = (SELECT IF(
(
SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
WHERE
(table_name = @tablename)
AND (table_schema = @dbname)
AND (column_name = @columnname)
) > 0,
"SELECT 1",
CONCAT("ALTER TABLE ", @tablename, " ADD ", @columnname, " INT UNSIGNED NOT NULL;")
));
PREPARE alterIfNotExists FROM @preparedStatement;
EXECUTE alterIfNotExists;
DEALLOCATE PREPARE alterIfNotExists;
SQL SERVER procedure:
DECLARE @dbname varchar(128), @tablename varchar(11), @columnname varchar(11), @preparedStatement varchar(max);
SET @dbname = DB_NAME();
SET @tablename = "my_table";
SET @columnname = "my_field";
SET @preparedStatement = (SELECT CASE(
(
SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
WHERE
(table_name = @tablename)
AND (table_schema = @dbname)
AND (column_name = @columnname)
) > 0,
"SELECT 1",
CONCAT("ALTER TABLE ", @tablename, " ADD ", @columnname, " INT UNSIGNED NOT NULL;")
));
EXEC sp_prepare @preparedStatement;
I got a syntax error on line 12 for the GREATER THAN operator:
Msg 102, Level 15, State 1, Line 12 Incorrect syntax near '>'.
mysql
sql-server stored-procedures mysql
sql-server stored-procedures edited Nov 20 '18 at 9:50
Larnu
16.8k41630
asked Nov 20 '18 at 9:48
MohammadMohammad
1,85922556
edited Nov 20 '18 at 9:50
Larnu
16.8k41630
asked Nov 20 '18 at 9:48
MohammadMohammad
1,85922556
edited Nov 20 '18 at 9:50
Larnu
16.8k41630
edited Nov 20 '18 at 9:50
Larnu
16.8k41630
edited Nov 20 '18 at 9:50
Larnu
16.8k41630
16.8k41630
asked Nov 20 '18 at 9:48
MohammadMohammad
1,85922556
asked Nov 20 '18 at 9:48
MohammadMohammad
1,85922556
asked Nov 20 '18 at 9:48
MohammadMohammad
1,85922556
1,85922556
Which version of SQL server are you using?
– Stuart Grant
Nov 20 '18 at 9:52
@StuartGrant version 14
– Mohammad
Nov 20 '18 at 9:55
add a comment |
Which version of SQL server are you using?
– Stuart Grant
Nov 20 '18 at 9:52
@StuartGrant version 14
– Mohammad
Nov 20 '18 at 9:55
Which version of SQL server are you using?
– Stuart Grant
Nov 20 '18 at 9:52
Which version of SQL server are you using?
– Stuart Grant
Nov 20 '18 at 9:52
@StuartGrant version 14
– Mohammad
Nov 20 '18 at 9:55
@StuartGrant version 14
– Mohammad
Nov 20 '18 at 9:55
add a comment |
2 Answers
2
active
oldest
votes
There was a lot wrong with your SQL:
Firstly, SQL Server uses single quotes (') for literal strings.
CASE expressions use the format CASE WHEN {expr} THEN {expr} ELSE [WHEN {expr} THEN {expr}] [THEN {expr}] END or CASE {expr} WHEN ...; your version was very malformed.
UNSIGNED datatypes don't exist in SQL Server. You only have signed (apart from tinyint, which is 0-255).
Also, sp_prepare isn't the right function, you want sp_executesql.
Finally, I've wrapped the object names with QUOTENAME to avoid injection, and change the datatypes to sysname (aka nvarchar(128)) and made the literal string an nvarchar (N'' rather than ''). This gets you:
DECLARE @dbname sysname,
@tablename sysname,
@columnname sysname,
@preparedStatement nvarchar(MAX);
SET @dbname = DB_NAME();
SET @tablename = N'my_table';
SET @columnname = N'my_field';
SET @preparedStatement = CASE
WHEN (SELECT COUNT(*)
FROM INFORMATION_SCHEMA.COLUMNS
WHERE (TABLE_NAME = @tablename)
AND (TABLE_SCHEMA = @dbname)
AND (COLUMN_NAME = @columnname)) = 0 THEN CONCAT(N'ALTER TABLE ', QUOTENAME(@tablename), N' ADD ', QUOTENAME(@columnname), N' INT NOT NULL;')
END;
EXEC sp_executesql @preparedStatement;
answered Nov 20 '18 at 9:59
LarnuLarnu
16.8k41630
thank you, I was expecting a lot of errors in my code because I have no experience in SQL SERVER. The procedure above executed successfully, but unfortunately, didn't alter the table.
– Mohammad
Nov 20 '18 at 10:08
@Mohammad it is because the logic in the CASE statement has been reversed. Should be easy to fix change >0 to =0
– t-clausen.dk
Nov 20 '18 at 10:10
Nice spot @t-clausen.dk
– Larnu
Nov 20 '18 at 10:23
add a comment |
Another way to look at the SQL and in IMO make it easier to read is to convert the CASE statement to an existence check
DECLARE @dbname varchar(128), @SchemaName varchar(30), @tablename varchar(30), @columnname varchar(30), @preparedStatement nvarchar(max);
SET @dbname = DB_NAME();
SET @SchemaName = 'dbo';
SET @tablename = 'myTable';
SET @columnname = 'myColumn';
IF NOT EXISTS(
SELECT * FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA LIKE @SchemaName
AND TABLE_NAME LIKE @tablename
AND COLUMN_NAME LIKE @columnname
)
BEGIN
SET @preparedStatement = CONCAT('ALTER TABLE ', @tablename, ' ADD ', @columnname, ' INT UNSIGNED NOT NULL;')
EXEC sp_executesql @preparedStatement;
END
answered Nov 20 '18 at 10:10
Stuart GrantStuart Grant
405211
2
the variable needs to be nvarchar in order to use sp_executesql
– t-clausen.dk
Nov 20 '18 at 10:13
Considering the nature of the SQL as well, you should be taking injection into consideration. Even avarchar(30)can be injected into.
– Larnu
Nov 20 '18 at 10:24
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%2f53390244%2fhow-to-add-new-column-if-its-not-exist%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
There was a lot wrong with your SQL:
Firstly, SQL Server uses single quotes (') for literal strings.
CASE expressions use the format CASE WHEN {expr} THEN {expr} ELSE [WHEN {expr} THEN {expr}] [THEN {expr}] END or CASE {expr} WHEN ...; your version was very malformed.
UNSIGNED datatypes don't exist in SQL Server. You only have signed (apart from tinyint, which is 0-255).
Also, sp_prepare isn't the right function, you want sp_executesql.
Finally, I've wrapped the object names with QUOTENAME to avoid injection, and change the datatypes to sysname (aka nvarchar(128)) and made the literal string an nvarchar (N'' rather than ''). This gets you:
DECLARE @dbname sysname,
@tablename sysname,
@columnname sysname,
@preparedStatement nvarchar(MAX);
SET @dbname = DB_NAME();
SET @tablename = N'my_table';
SET @columnname = N'my_field';
SET @preparedStatement = CASE
WHEN (SELECT COUNT(*)
FROM INFORMATION_SCHEMA.COLUMNS
WHERE (TABLE_NAME = @tablename)
AND (TABLE_SCHEMA = @dbname)
AND (COLUMN_NAME = @columnname)) = 0 THEN CONCAT(N'ALTER TABLE ', QUOTENAME(@tablename), N' ADD ', QUOTENAME(@columnname), N' INT NOT NULL;')
END;
EXEC sp_executesql @preparedStatement;
answered Nov 20 '18 at 9:59
LarnuLarnu
16.8k41630
thank you, I was expecting a lot of errors in my code because I have no experience in SQL SERVER. The procedure above executed successfully, but unfortunately, didn't alter the table.
– Mohammad
Nov 20 '18 at 10:08
@Mohammad it is because the logic in the CASE statement has been reversed. Should be easy to fix change >0 to =0
– t-clausen.dk
Nov 20 '18 at 10:10
Nice spot @t-clausen.dk
– Larnu
Nov 20 '18 at 10:23
add a comment |
There was a lot wrong with your SQL:
Firstly, SQL Server uses single quotes (') for literal strings.
CASE expressions use the format CASE WHEN {expr} THEN {expr} ELSE [WHEN {expr} THEN {expr}] [THEN {expr}] END or CASE {expr} WHEN ...; your version was very malformed.
UNSIGNED datatypes don't exist in SQL Server. You only have signed (apart from tinyint, which is 0-255).
Also, sp_prepare isn't the right function, you want sp_executesql.
Finally, I've wrapped the object names with QUOTENAME to avoid injection, and change the datatypes to sysname (aka nvarchar(128)) and made the literal string an nvarchar (N'' rather than ''). This gets you:
DECLARE @dbname sysname,
@tablename sysname,
@columnname sysname,
@preparedStatement nvarchar(MAX);
SET @dbname = DB_NAME();
SET @tablename = N'my_table';
SET @columnname = N'my_field';
SET @preparedStatement = CASE
WHEN (SELECT COUNT(*)
FROM INFORMATION_SCHEMA.COLUMNS
WHERE (TABLE_NAME = @tablename)
AND (TABLE_SCHEMA = @dbname)
AND (COLUMN_NAME = @columnname)) = 0 THEN CONCAT(N'ALTER TABLE ', QUOTENAME(@tablename), N' ADD ', QUOTENAME(@columnname), N' INT NOT NULL;')
END;
EXEC sp_executesql @preparedStatement;
answered Nov 20 '18 at 9:59
LarnuLarnu
16.8k41630
thank you, I was expecting a lot of errors in my code because I have no experience in SQL SERVER. The procedure above executed successfully, but unfortunately, didn't alter the table.
– Mohammad
Nov 20 '18 at 10:08
@Mohammad it is because the logic in the CASE statement has been reversed. Should be easy to fix change >0 to =0
– t-clausen.dk
Nov 20 '18 at 10:10
Nice spot @t-clausen.dk
– Larnu
Nov 20 '18 at 10:23
add a comment |
There was a lot wrong with your SQL:
Firstly, SQL Server uses single quotes (') for literal strings.
CASE expressions use the format CASE WHEN {expr} THEN {expr} ELSE [WHEN {expr} THEN {expr}] [THEN {expr}] END or CASE {expr} WHEN ...; your version was very malformed.
UNSIGNED datatypes don't exist in SQL Server. You only have signed (apart from tinyint, which is 0-255).
Also, sp_prepare isn't the right function, you want sp_executesql.
Finally, I've wrapped the object names with QUOTENAME to avoid injection, and change the datatypes to sysname (aka nvarchar(128)) and made the literal string an nvarchar (N'' rather than ''). This gets you:
DECLARE @dbname sysname,
@tablename sysname,
@columnname sysname,
@preparedStatement nvarchar(MAX);
SET @dbname = DB_NAME();
SET @tablename = N'my_table';
SET @columnname = N'my_field';
SET @preparedStatement = CASE
WHEN (SELECT COUNT(*)
FROM INFORMATION_SCHEMA.COLUMNS
WHERE (TABLE_NAME = @tablename)
AND (TABLE_SCHEMA = @dbname)
AND (COLUMN_NAME = @columnname)) = 0 THEN CONCAT(N'ALTER TABLE ', QUOTENAME(@tablename), N' ADD ', QUOTENAME(@columnname), N' INT NOT NULL;')
END;
EXEC sp_executesql @preparedStatement;
answered Nov 20 '18 at 9:59
LarnuLarnu
16.8k41630
There was a lot wrong with your SQL:
Firstly, SQL Server uses single quotes (') for literal strings.
CASE expressions use the format CASE WHEN {expr} THEN {expr} ELSE [WHEN {expr} THEN {expr}] [THEN {expr}] END or CASE {expr} WHEN ...; your version was very malformed.
UNSIGNED datatypes don't exist in SQL Server. You only have signed (apart from tinyint, which is 0-255).
Also, sp_prepare isn't the right function, you want sp_executesql.
Finally, I've wrapped the object names with QUOTENAME to avoid injection, and change the datatypes to sysname (aka nvarchar(128)) and made the literal string an nvarchar (N'' rather than ''). This gets you:
DECLARE @dbname sysname,
@tablename sysname,
@columnname sysname,
@preparedStatement nvarchar(MAX);
SET @dbname = DB_NAME();
SET @tablename = N'my_table';
SET @columnname = N'my_field';
SET @preparedStatement = CASE
WHEN (SELECT COUNT(*)
FROM INFORMATION_SCHEMA.COLUMNS
WHERE (TABLE_NAME = @tablename)
AND (TABLE_SCHEMA = @dbname)
AND (COLUMN_NAME = @columnname)) = 0 THEN CONCAT(N'ALTER TABLE ', QUOTENAME(@tablename), N' ADD ', QUOTENAME(@columnname), N' INT NOT NULL;')
END;
EXEC sp_executesql @preparedStatement;
answered Nov 20 '18 at 9:59
LarnuLarnu
16.8k41630
edited Nov 20 '18 at 10:23
answered Nov 20 '18 at 9:59
LarnuLarnu
16.8k41630
answered Nov 20 '18 at 9:59
LarnuLarnu
16.8k41630
answered Nov 20 '18 at 9:59
LarnuLarnu
16.8k41630
16.8k41630
thank you, I was expecting a lot of errors in my code because I have no experience in SQL SERVER. The procedure above executed successfully, but unfortunately, didn't alter the table.
– Mohammad
Nov 20 '18 at 10:08
@Mohammad it is because the logic in the CASE statement has been reversed. Should be easy to fix change >0 to =0
– t-clausen.dk
Nov 20 '18 at 10:10
Nice spot @t-clausen.dk
– Larnu
Nov 20 '18 at 10:23
add a comment |
thank you, I was expecting a lot of errors in my code because I have no experience in SQL SERVER. The procedure above executed successfully, but unfortunately, didn't alter the table.
– Mohammad
Nov 20 '18 at 10:08
@Mohammad it is because the logic in the CASE statement has been reversed. Should be easy to fix change >0 to =0
– t-clausen.dk
Nov 20 '18 at 10:10
Nice spot @t-clausen.dk
– Larnu
Nov 20 '18 at 10:23
thank you, I was expecting a lot of errors in my code because I have no experience in SQL SERVER. The procedure above executed successfully, but unfortunately, didn't alter the table.
– Mohammad
Nov 20 '18 at 10:08
thank you, I was expecting a lot of errors in my code because I have no experience in SQL SERVER. The procedure above executed successfully, but unfortunately, didn't alter the table.
– Mohammad
Nov 20 '18 at 10:08
@Mohammad it is because the logic in the CASE statement has been reversed. Should be easy to fix change >0 to =0
– t-clausen.dk
Nov 20 '18 at 10:10
@Mohammad it is because the logic in the CASE statement has been reversed. Should be easy to fix change >0 to =0
– t-clausen.dk
Nov 20 '18 at 10:10
Nice spot @t-clausen.dk
– Larnu
Nov 20 '18 at 10:23
Nice spot @t-clausen.dk
– Larnu
Nov 20 '18 at 10:23
add a comment |
Another way to look at the SQL and in IMO make it easier to read is to convert the CASE statement to an existence check
DECLARE @dbname varchar(128), @SchemaName varchar(30), @tablename varchar(30), @columnname varchar(30), @preparedStatement nvarchar(max);
SET @dbname = DB_NAME();
SET @SchemaName = 'dbo';
SET @tablename = 'myTable';
SET @columnname = 'myColumn';
IF NOT EXISTS(
SELECT * FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA LIKE @SchemaName
AND TABLE_NAME LIKE @tablename
AND COLUMN_NAME LIKE @columnname
)
BEGIN
SET @preparedStatement = CONCAT('ALTER TABLE ', @tablename, ' ADD ', @columnname, ' INT UNSIGNED NOT NULL;')
EXEC sp_executesql @preparedStatement;
END
answered Nov 20 '18 at 10:10
Stuart GrantStuart Grant
405211
2
the variable needs to be nvarchar in order to use sp_executesql
– t-clausen.dk
Nov 20 '18 at 10:13
Considering the nature of the SQL as well, you should be taking injection into consideration. Even avarchar(30)can be injected into.
– Larnu
Nov 20 '18 at 10:24
add a comment |
Another way to look at the SQL and in IMO make it easier to read is to convert the CASE statement to an existence check
DECLARE @dbname varchar(128), @SchemaName varchar(30), @tablename varchar(30), @columnname varchar(30), @preparedStatement nvarchar(max);
SET @dbname = DB_NAME();
SET @SchemaName = 'dbo';
SET @tablename = 'myTable';
SET @columnname = 'myColumn';
IF NOT EXISTS(
SELECT * FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA LIKE @SchemaName
AND TABLE_NAME LIKE @tablename
AND COLUMN_NAME LIKE @columnname
)
BEGIN
SET @preparedStatement = CONCAT('ALTER TABLE ', @tablename, ' ADD ', @columnname, ' INT UNSIGNED NOT NULL;')
EXEC sp_executesql @preparedStatement;
END
answered Nov 20 '18 at 10:10
Stuart GrantStuart Grant
405211
2
the variable needs to be nvarchar in order to use sp_executesql
– t-clausen.dk
Nov 20 '18 at 10:13
Considering the nature of the SQL as well, you should be taking injection into consideration. Even avarchar(30)can be injected into.
– Larnu
Nov 20 '18 at 10:24
add a comment |
Another way to look at the SQL and in IMO make it easier to read is to convert the CASE statement to an existence check
DECLARE @dbname varchar(128), @SchemaName varchar(30), @tablename varchar(30), @columnname varchar(30), @preparedStatement nvarchar(max);
SET @dbname = DB_NAME();
SET @SchemaName = 'dbo';
SET @tablename = 'myTable';
SET @columnname = 'myColumn';
IF NOT EXISTS(
SELECT * FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA LIKE @SchemaName
AND TABLE_NAME LIKE @tablename
AND COLUMN_NAME LIKE @columnname
)
BEGIN
SET @preparedStatement = CONCAT('ALTER TABLE ', @tablename, ' ADD ', @columnname, ' INT UNSIGNED NOT NULL;')
EXEC sp_executesql @preparedStatement;
END
answered Nov 20 '18 at 10:10
Stuart GrantStuart Grant
405211
Another way to look at the SQL and in IMO make it easier to read is to convert the CASE statement to an existence check
DECLARE @dbname varchar(128), @SchemaName varchar(30), @tablename varchar(30), @columnname varchar(30), @preparedStatement nvarchar(max);
SET @dbname = DB_NAME();
SET @SchemaName = 'dbo';
SET @tablename = 'myTable';
SET @columnname = 'myColumn';
IF NOT EXISTS(
SELECT * FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA LIKE @SchemaName
AND TABLE_NAME LIKE @tablename
AND COLUMN_NAME LIKE @columnname
)
BEGIN
SET @preparedStatement = CONCAT('ALTER TABLE ', @tablename, ' ADD ', @columnname, ' INT UNSIGNED NOT NULL;')
EXEC sp_executesql @preparedStatement;
END
answered Nov 20 '18 at 10:10
Stuart GrantStuart Grant
405211
edited Nov 20 '18 at 10:21
answered Nov 20 '18 at 10:10
Stuart GrantStuart Grant
405211
answered Nov 20 '18 at 10:10
Stuart GrantStuart Grant
405211
answered Nov 20 '18 at 10:10
Stuart GrantStuart Grant
405211
405211
2
the variable needs to be nvarchar in order to use sp_executesql
– t-clausen.dk
Nov 20 '18 at 10:13
Considering the nature of the SQL as well, you should be taking injection into consideration. Even avarchar(30)can be injected into.
– Larnu
Nov 20 '18 at 10:24
add a comment |
2
the variable needs to be nvarchar in order to use sp_executesql
– t-clausen.dk
Nov 20 '18 at 10:13
Considering the nature of the SQL as well, you should be taking injection into consideration. Even avarchar(30)can be injected into.
– Larnu
Nov 20 '18 at 10:24
2
2
the variable needs to be nvarchar in order to use sp_executesql
– t-clausen.dk
Nov 20 '18 at 10:13
the variable needs to be nvarchar in order to use sp_executesql
– t-clausen.dk
Nov 20 '18 at 10:13
Considering the nature of the SQL as well, you should be taking injection into consideration. Even a
varchar(30) can be injected into.– Larnu
Nov 20 '18 at 10:24
Considering the nature of the SQL as well, you should be taking injection into consideration. Even a
varchar(30) can be injected into.– Larnu
Nov 20 '18 at 10:24
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%2f53390244%2fhow-to-add-new-column-if-its-not-exist%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
Which version of SQL server are you using?
– Stuart Grant
Nov 20 '18 at 9:52
@StuartGrant version 14
– Mohammad
Nov 20 '18 at 9:55