ERROR IN DYNAMIC SQL: Conversion failed when converting the varchar value ', ' to data type int

Question

My purpose is to produce a table containing the table_name, column_name, number of row each column and number of null value in each column. But I get an error:

Conversion failed when converting the varchar value ', ' to data type int

These are my queries:

DECLARE @BANG TABLE 
              (
                  TABLE_NAME NVARCHAR(MAX), 
                  COLUMN_NAME NVARCHAR(MAX), 
                  ID INT IDENTITY(1, 1)
              )

INSERT INTO @BANG (TABLE_NAME, COLUMN_NAME)
    SELECT A.NAME AS TABLE_NAME, B.NAME AS COLUMN_NAME 
    FROM SYS.TABLES AS A
    LEFT JOIN SYS.COLUMNS AS B ON A.OBJECT_ID = B.OBJECT_ID
    WHERE 1=1 
      AND A.NAME IN ('CTHD', 'HOADON', 'SANPHAM', 'KHACHHANG', 'NHANVIEN')

DECLARE @RESULT TABLE 
                (
                    TABLE_NAME NVARCHAR(MAX), 
                    COLUMN_NAME NVARCHAR(MAX), 
                    TOTAL_ROW INT, 
                    TOTAL_NULL INT
                )

DECLARE @ID INT = 0

WHILE @ID <= (SELECT COUNT(*) FROM @BANG)
BEGIN
    DECLARE @TABLE_NAME NVARCHAR(MAX)
    SET @TABLE_NAME = (SELECT TABLE_NAME FROM @BANG WHERE @ID = ID)

    DECLARE @COLUMN_NAME NVARCHAR(MAX)
    SET @COLUMN_NAME = (SELECT COLUMN_NAME FROM @BANG WHERE ID = @ID)

    DECLARE @TOTAL_ROW INT
    DECLARE @TOTAL_NULL INT

    DECLARE @SQL NVARCHAR(MAX)
    SET @SQL = 'SET @TOTAL_ROW = (SELECT COUNT(*) FROM '+@TABLE_NAME+')
                SET @TOTAL_NULL = (SELECT COUNT(*) FROM '+@TABLE_NAME+' WHERE '+@COLUMN_NAME+' IS NULL)
                INSERT INTO @RESULT
                VALUES ('+@TABLE_NAME+', '+@COLUMN_NAME+', '+@TOTAL_ROW+', '+@TOTAL_NULL+')
                '

    SET @ID += 1

    EXEC (@SQL)
END

I need your help. Thanks in advance

Answer 1

You should be using parameterized SQL. But honestly, the code is such a mess that I'm not going to attempt that fix.

The problem is that parameters such as @TOTAL_ROW are integers, not strings. So, the + is treated as addition rather than string concatenation.

The simplest immediate fix is to use CONCAT():

  SET @SQL = CONCAT('
INSERT INTO @RESULT
    VALUES (''', @TABLE_NAME, ''', ''', @COLUMN_NAME, ''', ''', @TOTAL_ROW, ', ', @TOTAL_NULL, ')';

You may have the same error elsewhere in the code. You need to fix all places where you have a number and string combined with + and you intend string concatenation rather than addition.

However, the real fix is to not munge query strings with such values. Instead use sp_executesql passing the values in as parameters.

Answer 2

The conversion error is during the generation of the dynamic SQL query, not during execution of the statement.

There are a number of issues with the script in your question. Below is a script that uses QUOTENAME to more security build the SQL statement and uses a parameterized query to execute it. The WHILE pseudo cursor doesn't provide any value in this case so this version uses a real cursor.

DECLARE @RESULT TABLE (SCHEMA_NAME sysname, TABLE_NAME sysname, COLUMN_NAME sysname, TOTAL_ROW int, TOTAL_NULL int);
DECLARE @SQL nvarchar(MAX), @SchemaName sysname, @TableName sysname, @ColumnName sysname;
DECLARE BANG CURSOR LOCAL FAST_FORWARD FOR
    SELECT s.name AS SCHEMA_NAME, t.name AS TABLE_NAME, c.name AS COLUMN_NAME
    FROM sys.tables AS t
    JOIN sys.schemas AS s ON s.schema_id = t.schema_id
    JOIN sys.columns AS c ON c.object_id = t.object_id
    WHERE t.name IN (N'CTHD', N'HOADON', N'SANPHAM', N'KHACHHANG', N'NHANVIEN');
OPEN BANG;
WHILE 1 = 1
BEGIN
    FETCH NEXT FROM BANG INTO @SchemaName, @TableName, @ColumnName;
    IF @@FETCH_STATUS = -1 BREAK;
    SET @SQL = N'SELECT @SchemaName, @TableName, @ColumnName, COUNT(*), COALESCE(SUM(CASE WHEN ' + QUOTENAME(@ColumnName) + N' IS NULL THEN 1 ELSE 0 END),0)
    FROM ' + QUOTENAME(@SchemaName) + N'.' + QUOTENAME(@TableName) + N';'
    PRINT @SQL
    INSERT INTO @RESULT(SCHEMA_NAME, TABLE_NAME, COLUMN_NAME, TOTAL_ROW, TOTAL_NULL)
        EXEC sp_executesql @SQL
            , N'@SchemaName sysname, @TableName sysname, @ColumnName sysname'
            , @SchemaName = @SchemaName
            , @TableName = @TableName
            , @ColumnName = @ColumnName;
END;
CLOSE BANG;
DEALLOCATE BANG;
SELECT SCHEMA_NAME, TABLE_NAME, COLUMN_NAME, TOTAL_ROW, TOTAL_NULL
FROM @RESULT
ORDER BY SCHEMA_NAME, TABLE_NAME, COLUMN_NAME;
GO

If you don't have many tables/columns, you could use a single UNION ALL query and ditch the (pseudo)cursor entirely:

DECLARE @SQL nvarchar(MAX) = (SELECT STRING_AGG(
      N'SELECT ' + QUOTENAME(s.name,'''') + N' AS SCHEMA_NAME,' 
    + QUOTENAME(t.name, '''') + N' AS TABLE_NAME,' 
    + QUOTENAME(c.name,'''') + N' AS COLUMN_NAME,'
    + 'COUNT(*) AS TOTAL_ROW,'
    + 'COALESCE(SUM(CASE WHEN ' + QUOTENAME(c.name) + ' IS NULL THEN 1 ELSE 0 END),0) AS TOTAL_NULL '
    + 'FROM ' + QUOTENAME(s.name) + N'.' + QUOTENAME(t.name)
    , ' UNION ALL ') + N';'
    FROM sys.tables AS t
    JOIN sys.schemas AS s ON s.schema_id = t.schema_id
    JOIN sys.columns AS c ON c.object_id = t.object_id
    WHERE t.name IN (N'CTHD', N'HOADON', N'SANPHAM', N'KHACHHANG', N'NHANVIEN');
EXEC sp_executesql @SQL;

Answer 3

As Gordon said, use sp_execute properly to execute dynamics sql. And there's some others issue with this code, not related to the question.

1. "the more important" when investigating is to use the print statement before the exec statement, to know what's being execute. Then, you'll realize where the errors is and why it won't work....

2. the first execution of the loop is useless (or maybe this is the one that produce the error....). You initialize @Id with the value of 0 and then compare it with the value of the identity in the table @BANG, starting at 1. This result to @TABLE_NAME and @COLUMN_NAME set to NULL, thus, concatening string without using CONCAT will end up in a NULL value. Nothing is execute on the first loop.

However, using concat with null value will not result in a null value, but an incorrect value (query in your case). As an exemple, this code

SET @SQL = CONCAT('
INSERT INTO @RESULT
    VALUES (''', @TABLE_NAME, ''', ''', @COLUMN_NAME, ''', ''', @TOTAL_ROW, ', ', @TOTAL_NULL, ')';

will result in something like "INSERT INTO @RESULT VALUES (''CTHD'',''COL1'',,)" since both @TOTAL_ROW AND @TOTAL_NULL are null values. you need to use parametrized query using sp_executesql.

3. There's no need to execute two count on the same table, one for total rows, the second for null values. select count(1) return the total number of rows, and select count(@column_name) return the number of non-null value. So, Count(1) - count(@column_name) will gives you the number of null value. Then, use something like this to insert into @result :

   INSERT INTO @RESULT (...) SELECT TABLE_NAME, COLUMN_NAME, COUNT(1), COUNT(1) - COUNT(@COLUMN_NAME) FROM ...

4. when dealing with SQL Server object, you the quotename function. You'll never know when someone will put a space, quote, bracket or whatever in a schema/table/column name that might break you query.

5. Do not use "WHILE @ID <= (SELECT COUNT(*) FROM @BANG)" to test if there are more rows to process. Use a "WHILE EXISTS (SELECT 1 FROM @BANG)"