Case statment in dynamic SQL

Question

Hi!

I am trying to create a series of 'Alter table, Alter column' statements dynamically.

The problem is it automatically creates the column as Nullable, even if it should be not Null.
Im trying to put a CASE statment inside the dynamic SQL, but im doing something wrong.

  SELECT sql='alter   table   ['+d.name+']   alter   column   ['+a.name+']  char(' + CONVERT(VARCHAR(20), a.prec) + ')' 
         +  CASE(a.isnullable) WHEN 0 THEN ' Not NULL' WHEN 1 THEN ' NULL'
        ,d.name AS Tablename, b.name  AS datatype
  FROM syscolumns a 
       INNER JOIN  systypes  b  
          ON a.xtype=b.xusertype  
       INNER JOIN  sysobjects d 
          ON  a.id=d.id  
 WHERE d.xtype='U' AND d.name<>'dtproperties' 
       AND b.name IN ('varchar', 'nvarchar') 

Any idea how I should be doing this?

Thanks,

Zoe

Answer 1

Hi @zoe Ohara ,

Please try the following.

Notable points:

• IIF() instead of CASE statement.
• Use of QUOTENAME() function.

SQL

SELECT sql='alter table ' + QUOTENAME(d.name) + ' alter column ' + QUOTENAME(a.name) + ' char(' + CONVERT(VARCHAR(20), a.prec) + ')'   
 +  IIF(a.isnullable=0, ' NOT NULL', ' NULL')   
 ,d.name AS Tablename, b.name  AS datatype  
FROM syscolumns a   
    INNER JOIN  systypes  b    
        ON a.xtype=b.xusertype    
    INNER JOIN  sysobjects d   
        ON  a.id=d.id    
WHERE d.xtype='U' AND d.name<>'dtproperties'   
 AND b.name IN ('varchar', 'nvarchar') ;  

Answer 2

For the CASE itself try something like this:

CASE(a.isnullable) WHEN 1 THEN 'NULL' ELSE '' END

Answer 3

Thanks!

That did the trick!

Zoe x