一种更优雅的转义动态SQL的方式?

OK, so I've got this line of code in a search stored procedure:

我在搜索存储过程中有这一行代码

SET @where = 'job_code = ''' + REPLACE(@job_code, '''', '''''') + ''''

and there are basically two operations I'd like to streamline -the first being surrounding the concatenated value in single quotes. Obviously, in the above statement, I'm escaping a ' by using two '' and then ending the string with a ' so I can concatenate the actual value. There's got to be a better way!

基本上有两个操作我想要流线化-第一个是围绕在单引号的连接值。显然，在上面的语句中，我用两个来转义一个'，然后用一个'来结束字符串，这样我就可以把实际的值串联起来。一定有更好的办法!

The second of the operations would be the REPLACE(@job_code, '''', '''''') where I'm escaping any single quotes that might exist in the field.

第二个操作是REPLACE(@job_code， " "， " ")，在这里我将转义字段中可能存在的任何单引号。

Isn't there a much more elegant way of writing this line of code as a whole?

难道没有一种更优雅的方式来作为一个整体来编写这行代码吗?

I thought it was the ESCAPE keyword but that's tied tightly to the LIKE statement, so no go there.

我以为它是ESCAPE关键字，但它与LIKE语句紧密相连，所以不去那里。

4 个解决方案

#1

Not sure how you execute your sql query, if you use sp_executesql, could be something like this

不确定如何执行sql查询，如果使用sp_executesql，可能是这样的

EXECUTE sp_executesql 
          N'SELECT * FROM YouTable WHERE job_code = @job_code',
          N'@job_code varchar(100)',
          @job_code = @job_code;

#2

The parameterized query answer is probably the real "right answer", but to answer your original question, what you want is QUOTENAME(). More specifically, the single-quote version:

参数化查询答案可能是真正的“正确答案”，但是要回答最初的问题，您需要的是QUOTENAME()。更具体地说，单引号版本:

SET @where = 'job_code = ' + QUOTENAME(@job_code, '''')

Do note the length limit on this (input is a sysname, meaning 128 characters), though, as it is intended to quote the names of database objects and not as a general-purpose mechanism.

请注意这一点的长度限制(输入是一个sysname，意思是128个字符)，因为它的目的是引用数据库对象的名称，而不是作为一种通用的机制。

#3

You could declare constants:

你可以声明常量:

declare @SQ as char(1) = ''''

SET @where = 'job_code = ' + @SQ + REPLACE(@job_code, @SQ, @SQ + @SQ) + @SQ

#4

You could define a function that handles your typical scenarios, something like:

您可以定义一个处理典型场景的函数，例如:

create function WrapAndReplaceQuotes (@input as varchar(max))
returns varchar(max)
as
begin
    return '''' + replace(@input, '''', '''''') + ''''
end

SET @where = 'job_code = ' + WrapAndReplaceQuotes(@job_code)

#1