Case statment in dynamic SQL

zoe Ohara 286 Reputation points
2022-01-18T19:13:31.667+00:00

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

Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,563 questions
0 comments No comments
{count} votes

Accepted answer
  1. Yitzhak Khabinsky 25,206 Reputation points
    2022-01-18T19:48:40.927+00:00

    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') ;  
    
    0 comments No comments

2 additional answers

Sort by: Most helpful
  1. Michael Taylor 49,251 Reputation points
    2022-01-18T19:42:24.2+00:00

    For the CASE itself try something like this:

    CASE(a.isnullable) WHEN 1 THEN 'NULL' ELSE '' END
    
    1 person found this answer helpful.
    0 comments No comments

  2. zoe Ohara 286 Reputation points
    2022-01-18T20:39:15.89+00:00

    Thanks!

    That did the trick!

    Zoe x

    0 comments No comments