在参数化 SELECT 中使用转义字符时出现问题

Question

当我搜索包含转义字符的字段时，我在 SQL Server 中遇到了问题。当我直接搜索时，使用此代码，它工作正常：

SELECT distinct jobname, @jobname as Parm
FROM SurveyJob
where jobname = 'Head Coach - Women''s Volleyball'

但是如果我使用相同的语法在 where 子句中使用参数，我不会得到任何结果：

DECLARE @JobName varchar(150) = 'Head Coach – Women''s Volleyball'
SELECT distinct jobname, @jobname as Parm
FROM SurveyJob
where jobname = @jobname

我尝试使用 REPLACE 从参数和 WHERE 子句中的字段中去除撇号，但这似乎不起作用。

Answer 1

DECLARE @N VARCHAR(20) = 'Women''s Volleyball'
SELECT JobName FROM
(
SELECT JobName = 'Girl''s Volleyball'
UNION SELECT JobName = 'Women''s Volleyball'
UNION SELECT JobName = 'Men''s Volleyball'
UNION SELECT JobName = 'Boy''s Volleyball'
) x
WHERE JobName = @N
PRINT @N