在动态SQL中使用COALESCE删除NULL

I have some dynamic SQL as part of a stored procedure I want to execute:

我有一些动态SQL作为我想要执行的存储过程的一部分：

    SET @SQL_TXT = 'INSERT INTO ' +@ENTY_TABLE_NAME+
              '([ITEM_NAME]
               ,[ADD_DTT]
               ,[ADD_USR]
               ,[UPD_DTT]
               ,[UPD_USR]
               ,[ACTIVE_IND]
               ,[ITEM_PK])
         VALUES 
               ('''+@UPD_VALUE+'''
          , CURRENT_TIMESTAMP
          , '''+@UPD_USR_DOM_NAME+''', CURRENT_TIMESTAMP,'''+@UPD_USR_DOM_NAME+''',''Y'','''+@ITEM_PK+''');
          SET @Id = SCOPE_IDENTITY();
          RETURN;'

This runs fine, but ITEM_NAME can't be NULL so I want to ad a COALESE():

这样运行正常，但ITEM_NAME不能为NULL，所以我想要一个COALESE（）广告：

SET @SQL_TXT = 'INSERT INTO ' +@ENTY_TABLE_NAME+
          '(COALESCE([ITEM_NAME], '')
           ,[ADD_DTT]
           ,[ADD_USR]
           ,[UPD_DTT]
           ,[UPD_USR]
           ,[ACTIVE_IND]
           ,[ITEM_PK])
     VALUES 
           ('''+@UPD_VALUE+'''
      , CURRENT_TIMESTAMP
      , '''+@UPD_USR_DOM_NAME+''', CURRENT_TIMESTAMP,'''+@UPD_USR_DOM_NAME+''',''Y'','''+@ITEM_PK+''');
      SET @Id = SCOPE_IDENTITY();
      RETURN;'

But I am getting this error:

但是我收到了这个错误：

Incorrect syntax near the keyword 'COALESCE'.

Unclosed quotation mark after the character string ');

              SET @Id = SCOPE_IDENTITY();

              RETURN;'.

Incorrect syntax near '='.

For the life of me I don't see where this ')' is. What am I doing wrong?

对于我的生活，我不知道这个'）'在哪里。我究竟做错了什么？

Edit: here is the exec

编辑：这是执行官

EXECUTE SP_executesql @SQL_TXT, N'@Id INTEGER OUTPUT', @Id OUTPUT

1 个解决方案

#1

You can not put coalesce() around the column name destination of your insert, you use it around the value being inserted.

您不能将coalesce（）放在插入的列名称目标周围，而是在插入的值周围使用它。

  SET @SQL_TXT = 'INSERT INTO ' +@ENTY_TABLE_NAME+
              '([ITEM_NAME]
               ,[ADD_DTT]
               ,[ADD_USR]
               ,[UPD_DTT]
               ,[UPD_USR]
               ,[ACTIVE_IND]
               ,[ITEM_PK])
         VALUES 
               ('''+coalesce(@UPD_VALUE,'')+'''
          , CURRENT_TIMESTAMP
          , '''+@UPD_USR_DOM_NAME+''', CURRENT_TIMESTAMP,'''+@UPD_USR_DOM_NAME+''',''Y'','''+@ITEM_PK+''');
          SET @Id = SCOPE_IDENTITY();
          RETURN;'

---

Note: @UPD_USR_DOM_NAME is inserted into two different columns. Not sure if that is intentional, just thought I would point it out.

You can also fully parameterize the rest of your values for use with sp_executesql instead of concatenating them like that. (Guessing at the data types of your parameters in this example)

您还可以完全参数化其余值以与sp_executesql一起使用，而不是像这样连接它们。 （在此示例中猜测参数的数据类型）

declare @sql nvarchar(max);
declare @params nvarchar(max);
declare @id int;

set @sql = N'INSERT INTO ' +@ENTY_TABLE_NAME+'([ITEM_NAME] ,[ADD_DTT] ,[ADD_USR] ,[UPD_DTT] ,[UPD_USR] ,[ACTIVE_IND] ,[ITEM_PK])
      VALUES (coalesce(@UPD_VALUE,''), CURRENT_TIMESTAMP, @UPD_USR_DOM_NAME, CURRENT_TIMESTAMP,@UPD_USR_DOM_NAME,''Y'',@ITEM_PK);
      SET @Id = SCOPE_IDENTITY();
      RETURN;'

set @params = N'@UPD_VALUE varchar(32),@UPD_USR_DOM_NAME varchar(32), @ITEM_PK varchar(32), @Id INTEGER OUTPUT';

EXECUTE SP_executesql @sql, @params, @UPD_Value, @UPD_USER_DOM_NAME, @ITEM_PK, @Id = @Id OUTPUT;

dynamic sql reference:

动态sql参考：

• The curse and blessings of dynamic SQL - Erland Sommarskog
• 动态SQL的诅咒和祝福 - Erland Sommarskog
• sp_executesql
• sp_executesql的