Error reading a concatenate command

Question

I'm trying to run a recursive alter table reading a couple of parameters from my sys table.

DECLARE @AlterCmd NVARCHAR(MAX)

SELECT top 1
        @AlterCmd = 'ALTER TABLE [dbo].[table] ALTER COLUMN ' + COLUMN_NAME + ' NVARCHAR(' + CHARACTER_MAXIMUM_LENGTH + ')'
    FROM 
        information_schema.columns 
    WHERE 
        table_name = 'table_name' and DATA_TYPE like '%char%'

print @AlterCmd

In this sample code I just want to validate if the command is coming right but it's not possible to run it due an error in the concatenate. According to the error message I get:

Msg 245, Level 16, State 1, Line 63
Conversion failed when converting the varchar value ')' to data type int.

score 6 · Answer 1 · edited Dec 21 '17 at 22:27

There are a couple things wrong:

You should always use QUOTENAME() when dealing with concatenated values, both from the perspective that it can prevent SQL injection attacks, and because it prevents errors when invalid characters get used.
Whenever you're concatenating values, you should use NVARCHAR literals and explicit converts to your desired data type and don't rely on implicit conversions.
Use statement terminators.

Code:

DECLARE @AlterCmd NVARCHAR(MAX);

SELECT TOP 1 @AlterCmd = N'ALTER TABLE [dbo].[table] ALTER COLUMN '
                         + QUOTENAME(COLUMN_NAME)
                         + N' NVARCHAR('
                         + CONVERT(NVARCHAR(10), CHARACTER_MAXIMUM_LENGTH)
                         + N');'
FROM   INFORMATION_SCHEMA.COLUMNS
WHERE  TABLE_NAME = N'table_name'
       AND DATA_TYPE LIKE N'%char%';

PRINT @AlterCmd;

McNets · Accepted Answer · 2017-12-21T22:39:20.203

You can use CONCAT for this purpose. Note I've added TABLE_SCHEMA and TABLE_NAME just to build table name.

CREATE TABLE TEST(ID INT, VAL CHAR(10), NVAL NVARCHAR(20));
GO

✓

DECLARE @AlterCmd NVARCHAR(MAX)
SELECT @AlterCmd = CONCAT('ALTER TABLE ', 
                          QUOTENAME(TABLE_SCHEMA),
                          '.',
                          QUOTENAME(TABLE_NAME),
                          ' ALTER COLUMN ', 
                          QUOTENAME(COLUMN_NAME), 
                          ' NVARCHAR(', 
                          CONVERT(VARCHAR(20), CHARACTER_MAXIMUM_LENGTH), 
                          ');')
FROM  INFORMATION_SCHEMA.COLUMNS 
WHERE table_name = 'TEST'
AND   DATA_TYPE <> 'NVARCHAR'
AND   DATA_TYPE LIKE '%char%'
SELECT @AlterCmd
GO

| (No column name)                                          |
| :-------------------------------------------------------- |
| ALTER TABLE [dbo].[TEST] ALTER COLUMN [VAL] NVARCHAR(10); |

dbfiddle here

Error reading a concatenate command

2 Answers2