CODEWITHSUNDEEP
sql-servertypes
Arash
Arash

Reputation: 3051

Getting type of column in SQL Server

For example I want to do something like this :

//if the Column type is nvarchar
//do something

//if the Column type is int
//do something

EDITED:

for example :

i have a table with columns(col1 is int,col2 is nvarchar),and i have data in this table,for example 

col1 : 1,2,3,4,NULL and col2 : a,b,d,f,E,NULL

now i want to fill NULL data of col1 with 0 and NULL data of col2 with " "

Upvotes: 5

Views: 25693

Answers (3)

Aaron Bertrand
Aaron Bertrand

Reputation: 280252

If you know the table and the column name, then:

DECLARE @tn sysname;

SELECT @tn = TYPE_NAME(system_type_id) 
FROM sys.columns 
WHERE name = @column_name 
AND [object_id] = OBJECT_ID(N'dbo.tablename');

IF @tn = N'nvarchar'
     DECLARE @x nvarchar(32);

IF @tn = N'int'
     DECLARE @i int;

However note that you won't be able to declare the same variable name with different data types this way, even if SQL Server will only ever reach one of them. You will get something like:

Msg 134, Level 15, State 1
The variable name '@i' has already been declared. Variable names must be unique within a query batch or stored procedure.

If you know the name of the table then you can build a dynamic SQL statement like you propose as follows (note that this only covers a subset of types - but should give you the idea):

DECLARE @table nvarchar(512) = N'dbo.whatever';

DECLARE @sql nvarchar(max) = N'SELECT ';

SELECT @sql = @sql 
    + STUFF((SELECT N',' + QUOTENAME(c.name) + N' = COALESCE(' 
    + QUOTENAME(c.name) + N',' + CASE
      WHEN t.name LIKE N'%int' THEN N'0'
      WHEN t.name LIKE N'%char' THEN N''' '''
      END + N')'
 FROM sys.types AS t
INNER JOIN sys.columns AS c 
ON t.system_type_id = c.system_type_id
AND t.user_type_id = c.user_type_id
WHERE c.[object_id] = OBJECT_ID(@table)
ORDER BY c.column_id
FOR XML PATH(''), 
TYPE).value(N'./text()[1]', N'nvarchar(max)'), 1, 1, N'');

SET @sql = @sql + N' FROM ' + @table;
--SET @sql = @sql + N' WHERE...'

EXEC sys.sp_executesql @sql;

I don't know how you could even dream of doing this without dynamic SQL. And ugh, that's a lot of work to prevent your presentation tier from having to deal with NULLs. That is probably the better place to deal with this.

Upvotes: 18

pankaj bhavsar
pankaj bhavsar

Reputation: 21

SELECT  typ.name, c_name,CASE when system_type_id <> 243  then typ.max_length 
                              when system_type_id = 243 then   c_maxlen
                         END AS [Maxlen1] ,
                         CASE when system_type_id <> 243  then typ.precision 
                              when system_type_id = 243 then   c_precision
                         END AS [precision] ,

                         CASE when system_type_id <> 243  then typ.scale 
                              when system_type_id = 243 then   c_scale
                         END AS [scale] ,

                         CASE when system_type_id <> 243  then typ.is_nullable 
                              when system_type_id = 243 then   c_nullability
                         END AS [nullabilty] ,
                         CASE 
                         WHEN system_type_id = 34    THEN 'image'
             WHEN (system_type_id = 35 OR cid = 35)    THEN 'text'
             WHEN (system_type_id = 36  OR cid = 36)  THEN 'uniqueidentifier'
             WHEN (system_type_id = 40  OR cid = 40)  THEN 'date'
             WHEN (system_type_id = 41  OR cid = 41)  THEN 'time'
             WHEN (system_type_id = 42  OR cid = 42)  THEN 'datetime2'
             WHEN (system_type_id = 48  OR cid = 48)  THEN 'tinyint'
             WHEN (system_type_id = 52  OR cid = 52)  THEN 'smallint'
             WHEN (system_type_id = 56  OR cid = 56)  THEN 'int'
             WHEN (system_type_id = 58  OR cid = 58)  THEN 'smalldatetime'
             WHEN (system_type_id = 59  OR cid = 59)  THEN 'real'
             WHEN (system_type_id = 60  OR cid = 60)  THEN 'money'
             WHEN (system_type_id = 61  OR cid = 61)  THEN 'datetime'
             WHEN (system_type_id = 62  OR cid = 62)  THEN 'float'
             WHEN (system_type_id = 98  OR cid = 98)  THEN 'sql_variant'
             WHEN (system_type_id = 99  OR cid = 99)  THEN 'ntext'
             WHEN (system_type_id = 104 OR cid = 104)   THEN 'bit'
             WHEN (system_type_id = 106 OR cid = 106)  THEN 'decimal'
             WHEN (system_type_id = 108 OR cid = 108)  THEN 'numeric'
             WHEN (system_type_id = 122 OR cid = 122)  THEN 'smallmoney'
             WHEN (system_type_id = 127 OR cid = 127)  THEN 'bigint'
             WHEN (system_type_id = 165 OR cid = 165)  THEN 'varbinary'
             WHEN (system_type_id = 167 OR cid = 167)  THEN 'varchar'
             WHEN (system_type_id = 173 OR cid = 173)  THEN 'binary'
             WHEN (system_type_id = 175 OR cid = 175)  THEN 'char'
             WHEN (system_type_id = 189 OR cid = 189)  THEN 'timestamp'
             WHEN (system_type_id = 231 OR cid = 231)  THEN 'nvarchar'
             WHEN (system_type_id = 239 OR cid = 239)  THEN 'nchar'
             WHEN (system_type_id = 241 OR cid = 241)  THEN 'xml'
--             when (system_type_id = 243 OR cid = 243)  THEN 'TABLE'
         END AS [Type]
  FROM sys.types as typ  left outer join (select table_types.name tt_name, columns.name c_name ,columns.max_length c_maxlen ,columns.precision c_precision ,columns.scale c_scale,columns.is_nullable c_nullability ,columns.system_type_id cid
                                          from sys.table_types , sys.columns  
                                          where columns.object_id = table_types.type_table_object_id)  tt
 on  (typ.name = tt.tt_name)                           
 where typ.is_user_defined = 1

Upvotes: 0

rssllbrns
rssllbrns

Reputation: 21

is this what you were looking for?

declare @columns as table
(col1 int,col2 nvarchar)

insert @columns
values (1,'a'),(2,'b'),(3,'d'),(4,'f'),(null,null)

select isnull(col1,0), isnull(col2,'') from @columns

Upvotes: 0

Related Questions