CASE Statement using Dynamic SQL query

shantanu97 · March 3, 2022, 9:17am

I am trying to build a CASE WHEN statement by using Dynamic SQL but I am getting the error. Could someone share your thoughts on how to resolve this error.

Below is a working dynamic SQL query.

DECLARE @SQL VARCHAR(MAX);
DECLARE @TableName SYSNAME;

SET @TableName = 'XYZ';
SET @SQL = '';

SELECT @SQL
    = @SQL + 'SELECT ' + QUOTENAME(sc.[Name], '''') + ' AS ColumnName, ' + QUOTENAME(t.[Name], '''') + ' AS DataType, '
      + QUOTENAME(sc.[Precision], '''') + ' AS precision, ' + QUOTENAME(sc.[Scale], '''') + ' AS scale, '
      + QUOTENAME(sc.[max_length], '''') + ' AS SetLength,MAX(DATALENGTH(ISNULL( ' + QUOTENAME(sc.[Name])
      + ',0))) AS MaxLength FROM ' + @TableName + CHAR(10) + ' UNION '
FROM [sys].[COLUMNS] sc
    INNER JOIN [sys].[types] t
        ON t.[system_type_id] = sc.[system_type_id]
           AND t.[Name] != 'sysname'
WHERE sc.OBJECT_ID = OBJECT_ID(@TableName);

SET @SQL = LEFT(@SQL, LEN(@SQL) - 6);

PRINT @SQL;

EXECUTE (@SQL);

What I want to do is that I want to do case when statement on name field column from sys.types table using dynamic SQL.

select t.[name],
CASE 
	WHEN t.[name] IN ('varchar','char','datetime','smalldatetime','bit') THEN 'C'
	WHEN t.[name] IN ('decimal','int') THEN 'N'
	END AS [name2]
FROM sys.columns sc
INNER JOIN sys.types t
	ON t.system_type_id = sc.system_type_id
		AND t.name != 'sysname'

Till now, my SQL query. This dynamic sql query is giving error.

DECLARE @SQL VARCHAR(MAX);
DECLARE @TableName SYSNAME;

SET @TableName = 'Collar';
SET @SQL = '';

SELECT @SQL
    = @SQL + 'SELECT ' + QUOTENAME(sc.[Name], '''') + ' AS ColumnName, ' + QUOTENAME(t.[Name], '''') + ' AS DataType, '
      + QUOTENAME(sc.PRECISION, '''') + ' AS precision, ' + QUOTENAME(sc.[scale], '''') + ' AS scale, case when  '
      + QUOTENAME(t.[Name]) + ''' IN (''varchar'',''nvarchar'',''char'',''bit'')'' then ''v'' when '
      + QUOTENAME(t.[Name]) + + ''' IN (''decimal'',''int'')'' then ''N'' '+ ' end as ' + ' AS DataType2, ' + QUOTENAME(sc.[max_length], '''')
      + ' AS SetLength,MAX(DATALENGTH(ISNULL( ' + QUOTENAME(sc.[Name]) + ',0))) AS MaxLength FROM ' + @TableName
      + CHAR(10) + ' UNION '
FROM [sys].[COLUMNS] sc
    INNER JOIN [sys].[types] t
        ON t.[system_type_id] = sc.[system_type_id]
           AND t.[Name] != 'sysname'
WHERE sc.OBJECT_ID = OBJECT_ID(@TableName);

SET @SQL = LEFT(@SQL, LEN(@SQL) - 6);

PRINT @SQL;

EXECUTE (@SQL);

Error:

Incorrect syntax near '('.
Case expressions may only be nested to level 10.

yosiasz · March 3, 2022, 12:07pm

Show us result of above ?

mike01 · March 3, 2022, 3:31pm

strings need single quote around them. As yosiasz said, we can't see what you are creating, but start with

'SELECT ''' + QUOTENAME(sc.[Name], '''') + ''' AS ColumnName

shantanu97 · March 3, 2022, 11:25pm

shantanu97:

select t.[name],
CASE 
	WHEN t.[name] IN ('varchar','char','datetime','smalldatetime','bit') THEN 'C'
	WHEN t.[name] IN ('decimal','int') THEN 'N'
	END AS [name2]
FROM sys.columns sc
INNER JOIN sys.types t
	ON t.system_type_id = sc.system_type_id
		AND t.name != 'sysname'

SELECT 'LandholderID' AS ColumnName, 'int' AS DataType, '10' AS precision, '0' AS scale, case when  [int]' IN ('varchar','nvarchar','char','bit')' then 'v' when [int]' IN ('decimal','int')' then 'N'  end as  AS DataType2, '4' AS SetLength,MAX(DATALENGTH(ISNULL( [LandholderID],0))) AS MaxLength FROM Collar
 UNION SELECT 'CompensationPaid' AS ColumnName, 'int' AS DataType, '10' AS precision, '0' AS scale, case when  [int]' IN ('varchar','nvarchar','char','bit')' then 'v' when [int]' IN ('decimal','int')' then 'N'  end as  AS DataType2, '4' AS SetLength,MAX(DATALENGTH(ISNULL( [CompensationPaid],0))) AS MaxLength FROM Collar
 UNION SELECT 'RehabDone' AS ColumnName, 'int' AS DataType, '10' AS precision, '0' AS scale, case when  [int]' IN ('varchar','nvarchar','char','bit')' then 'v' when [int]' IN ('decimal','int')' then 'N'  end as  AS DataType2, '4' AS SetLength,MAX(DATALENGTH(ISNULL( [RehabDone],0))) AS MaxLength FROM Collar
 UNION SELECT 'CorePhotosTaken' AS ColumnName, 'int' AS DataType, '10' AS precision, '0' AS scale, case when  [int]' IN ('varchar','nvarchar','char','bit')' then 'v' when [int]' IN ('decimal','int')' then 'N'  end as  AS DataType2, '4' AS SetLength,MAX(DATALENGTH(ISNULL( [CorePhotosTaken],0))) AS MaxLength FROM Collar
 UNION SELECT 'VerifiedFlag' AS ColumnName, 'int' AS DataType, '10' AS precision, '0' AS scale, case when  [int]' IN ('varchar','nvarchar','char','bit')' then 'v' when [int]' IN ('decimal','int')' then 'N'  end as  AS DataType2, '4' AS SetLength,MAX(DATALENGTH(ISNULL( [VerifiedFlag],0))) AS MaxLength FROM Collar
 UNION SELECT 'DateLoaded' AS ColumnName, 'smalldatetime' AS DataType, '16' AS precision, '0' AS scale, case when  [smalldatetime]' IN ('varchar','nvarchar','char','bit')' then 'v' when [smalldatetime]' IN ('decimal','int')' then 'N'  end as  AS DataType2, '4' AS SetLength,MAX(DATALENGTH(ISNULL( [DateLoaded],0))) AS MaxLength FROM Collar
 UNION SELECT 'DateModified' AS ColumnName, 'smalldatetime' AS DataType, '16' AS precision, '0' AS scale, case when  [smalldatetime]' IN ('varchar','nvarchar','char','bit')' then 'v' when [smalldatetime]' IN ('decimal','int')' then 'N'  end as  AS DataType2, '4' AS SetLength,MAX(DATALENGTH(ISNULL( [DateModified],0))) AS MaxLength FROM Collar
 UNION SELECT 'Exclude' AS ColumnName, 'bit' AS DataType, '1' AS precision, '0' AS scale, case when  [bit]' IN ('varchar','nvarchar','char','bit')' then 'v' when [bit]' IN ('decimal','int')' then 'N'  end as  AS DataType2, '1' AS SetLength,MAX(DATALENGTH(ISNULL( [Exclude],0))) AS MaxLength FROM Collar
 UNION SELECT 'PlannedDepth' AS ColumnName, 'decimal' AS DataType, '10' AS precision, '2' AS scale, case when  [decimal]' IN ('varchar','nvarchar','char','bit')' then 'v' when [decimal]' IN ('decimal','int')' then 'N'  end as  AS DataType2, '9' AS SetLength,MAX(DATALENGTH(ISNULL( [PlannedDepth],0))) AS MaxLength FROM Collar
 UNION SELECT 'HoleID' AS ColumnName, 'varchar' AS DataType, '0' AS precision, '0' AS scale, case when  [varchar]' IN ('varchar','nvarchar','char','bit')' then 'v' when [varchar]' IN ('decimal','int')' then 'N'  end as  AS DataType2, '50' AS SetLength,MAX(DATALENGTH(ISNULL( [HoleID],0))) AS MaxLength FROM Collar
 UNION SELECT 'Project' AS ColumnName, 'varchar' AS DataType, '0' AS precision, '0' AS scale, case when  [varchar]' IN ('varchar','nvarchar','char','bit')' then 'v' when [varchar]' IN ('decimal','int')' then 'N'  end as  AS DataType2, '50' AS SetLength,MAX(DATALENGTH(ISNULL( [Project],0))) AS MaxLength FROM Collar
 UNION SELECT 'HoleType' AS ColumnName, 'varchar' AS DataType, '0' AS precision, '0' AS scale, case when  [varchar]' IN ('varchar','nvarchar','char','bit')' then 'v' when [varchar]' IN ('decimal','int')' then 'N'  end as  AS DataType2, '50' AS SetLength,MAX(DATALENGTH(ISNULL( [HoleType],0))) AS MaxLength FROM Collar
 UNION SELECT 'VerifiedComments' AS ColumnName, 'varchar' AS DataType, '0' AS precision, '0' AS scale, case when  [varchar]' IN ('varchar','nvarchar','char','bit')' then 'v' when [varchar]' IN ('decimal','int')' then 'N'  end as  AS DataType2, '-1' AS SetLength,MAX(DATALENGTH(ISNULL( [VerifiedComments],0))) AS MaxLength FROM Collar
 UNION SELECT 'SourceFile' AS ColumnName, 'varchar' AS DataType, '0' AS precision, '0' AS scale, case when  [varchar]' IN ('varchar','nvarchar','char','bit')' then 'v' when [varchar]' IN ('decimal','int')' then 'N'  end as  AS DataType2, '255' AS SetLength,MAX(DATALENGTH(ISNULL( [SourceFile],0))) AS MaxLength FROM Collar
 UNION SELECT 'LoadedBy' AS ColumnName, 'varchar' AS DataType, '0' AS precision, '0' AS scale, case when  [varchar]' IN ('varchar','nvarchar','char','bit')' then 'v' when [varchar]' IN ('decimal','int')' then 'N'  end as  AS DataType2, '50' AS SetLength,MAX(DATALENGTH(ISNULL( [LoadedBy],0))) AS MaxLength FROM Collar
 UNION SELECT 'ModifiedBy' AS ColumnName, 'varchar' AS DataType, '0' AS precision, '0' AS scale, case when  [varchar]' IN ('varchar','nvarchar','char','bit')' then 'v' when [varchar]' IN ('decimal','int')' then 'N'  end as  AS DataType2, '50' AS SetLength,MAX(DATALENGTH(ISNULL( [ModifiedBy],0))) AS MaxLength FROM Collar
 UNION SELECT 'ts' AS ColumnName, 'timestamp' AS DataType, '0' AS precision, '0' AS scale, case when  [timestamp]' IN ('varchar','nvarchar','char','bit')' then 'v' when [timestamp]' IN ('decimal','int')' then 'N'  end as  AS DataType2, '8' AS SetLength,MAX(DATALENGTH(ISNULL( [ts],0))) AS MaxLength FROM Collar

shantanu97 · March 3, 2022, 11:27pm

Please Check my reply

yosiasz · March 3, 2022, 11:46pm

syntax error and logic error

when [varchar]' IN ('decimal','int')'

yosiasz · March 4, 2022, 1:55am

Basically what are you attempting to do, what are you trying to solve? What you have will not work as you are doing that case statement on a table that does not have that sys.types.[name] column.

mike01 · March 4, 2022, 7:29pm

I see this too (as as)

end as AS DataType2.

this might work for you

select  sc.name as ColumnName, t.[name] as DataType,
		sc.Precision, sc.Scale, 
		case when t.name IN ('varchar','nvarchar','char','bit') then 'v' 
		     when t.name IN ('decimal','int') then 'N'  
		end as DataType2, 
		'4' AS SetLength		
FROM sys.columns sc
INNER JOIN sys.types t
	ON t.system_type_id = sc.system_type_id
		AND t.name != 'sysname'