sql使用单引号追加值 - 字符串连接

This extended version on my previous question sql Append for value with single quotes which am trying to fix

我之前的问题sql上的这个扩展版本使用单引号追加值，我试图修复它

DECLARE @fNAME varchar(40) = 'O'brain'
DECLARE @query nvarchar(256)
DECLARE @id nvarchar(5) = '8'
SET @query = 'UPDATE TableName SET columnname = ''' + @fNAME + '''' +'Added on '+ GETDATE() + ''' WHERE Id= ' + convert(nvarchar, @id)

am trying to get below output using EXEC sp_executesql

我试图使用EXEC sp_executesql获得低于输出

Update table name set columnname = 'O''brain Added on Aug  8 2017 11:15AM' where id = 8

Basically am trying to append some text and current date along with name to be updates as text in one column

基本上我试图将一些文本和当前日期以及名称附加到一列中作为文本更新

2 个解决方案

#1

I would define and execute the query as:

我将定义并执行查询：

DECLARE @fNAME varchar(40) = 'O''brain';
DECLARE @query nvarchar(256);    
DECLARE @id nvarchar(5) = '8';

SET @query = '
UPDATE TableName
    SET columnname = @fNAME + '' added on '' + convert(varchar(255), GETDATE() )
    WHERE Id = @id';

EXEC sp_executesql @query, N'@fname varchar(40), @id nvarchar(5)', @fname=@fname, @id=@id;

Note: This is not going to format the date exactly as in your question. You haven't chosen a formatting for the date in your code, so I didn't either.

注意：这不会完全按照您的问题格式化日期。您没有为代码中的日期选择格式，所以我也没有。

#2

Here is how you would do this with a normal update. There appears to be no reason at all to use dynamic sql here.

以下是通过正常更新执行此操作的方法。似乎没有理由在这里使用动态SQL。

DECLARE @fNAME varchar(40) = 'O''brain';
DECLARE @id nvarchar(5) = '8';

Update TableName
set columnname = @fNAME + ' added on ' + convert(varchar(255), getdate())

#1