sql 用单引号附加值

问题描述

我有以下查询

DECLARE @fNAME varchar(40) = 'Obrain'
DECLARE @query nvarchar(256)
SET @query = 'UPDATE TableName SET columnname = ''' + @fNAME + ''''
select @query

结果符合预期

UPDATE TableName SET columnname = 'Obrain' 

但是如果我用单引号改变输入@fname

But if i change input @fname with single quotes

    DECLARE @fNAME varchar(40) = 'O''brain'
    DECLARE @query nvarchar(256)
    SET @query = 'UPDATE TableName SET columnname = ''' + @fNAME + ''''
    select @query

我得到以下结果，其中单引号有问题

i get below result where am having issue with single quotes

UPDATE TableName SET columnname = 'O'brain'

1. 我如何通过细微的更改修复此查询

1. How do i fix this query with minor changes

有什么更好的方法可以做到这一点

What is the better way to do this

推荐答案

正确的方法是什么?使用 sp_executesql 和参数:

The right way to do this? Use sp_executesql and parameters:

DECLARE @fNAME varchar(40) = 'O''brain';
DECLARE @query nvarchar(256);
SET @query = '
    UPDATE TableName SET columnname = @fNAME'

EXEC sp_executesql @query, N'@fNAME varchar(40)', @fName=@fName;