Getting type of column in SQL Server

Question

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 " "

Answer 1

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.