CODEWITHSUNDEEP
sqlsql-server
MissCoder87
MissCoder87

Reputation: 2669

Nested IF statements SQL

I can't seem to get the format right for my nested if. Basically what I'm trying to do is run

 select columnproperty(object_id(@TableName),t.name,'IsIdentity') = 0

against the loop created below. If it's an Idendity colum, I don't want it adding

DECLARE @TableName VARCHAR(50)
SELECT @TableName ='Users'
select -1 as colid, 'public class obj_' + @TableName + '{'
union

SELECT c.colid,
             'public '+
              (CASE WHEN t.name IN ('int', 'bigint') THEN 'Int32?' 
                   WHEN t.name IN ('nvarchar','varchar','text','ntext') THEN 'String'
                   WHEN t.name IN ('datetime','smalldatetime') THEN 'DateTime?'
                   WHEN t.name IN ('decimal') THEN 'Decimal?'
                   WHEN t.name IN ('bit') THEN 'Boolean?'
                   ELSE 'Unknown'
               END) + ' ' + c.name + '{ get; set; }'
       FROM syscolumns c JOIN
            systypes t
            ON t.xusertype=c.xusertype
       WHERE id IN (SELECT id FROM sysobjects WHERE name = @TableName) 
          union 
          select 1000 as colid, '}'

Upvotes: 2

Views: 118

Answers (1)

Alex K.
Alex K.

Reputation: 175766

Use the sys views, filter with sys.columns.is_identity

SELECT c.column_id,
         'public '+
          (CASE WHEN t.name IN ('int', 'bigint') THEN 'Int32?' 
               WHEN t.name IN ('nvarchar','varchar','text','ntext') THEN 'String'
               WHEN t.name IN ('datetime','smalldatetime') THEN 'DateTime?'
               WHEN t.name IN ('decimal') THEN 'Decimal?'
               WHEN t.name IN ('bit') THEN 'Boolean?'
               ELSE 'Unknown'
           END) + ' ' + c.name + '{ get; set; }'
    FROM    
        sys.columns c
    INNER JOIN 
        sys.types t ON c.user_type_id = t.user_type_id
    WHERE 
       c.object_id = OBJECT_ID(@TableName)
    AND
        c.is_identity = 0
    UNION 
        select 1000 as colid, '}'

Upvotes: 1

Related Questions