Question

例如我想做这样的事情:

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

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

<强度 > 编辑:

例如:

i 有一个包含列( col1 is int, col2 is nvarchar) 的表格, 并且我在这个表格中有数据, 例如

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

现在我要用 0 和 NULLL 的CO2 数据填充 Col1 的 NULLL 数据 "

Answer 1

如果您知道表格和列名,那么:

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;

然而请注意, 您将无法以这种方式声明数据类型不同的 < em> same 变量名称, 即使 SQL 服务器只达到其中之一。您将会得到类似 :

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.

如果您知道表格的名称,您可以按照以下提议建立一个动态 SQL 语句(请注意,这只包括种类的子集,但应该给你这个想法):

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;

我不知道你怎么能梦想在没有动态SQL的情况下做这个。和Ugh, 做很多工作来防止你的演示级别不得不处理NULLs。这可能是处理这件事的最好地方。

Answer 2

这就是你要找的吗?

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

Answer 3

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

友情链接