一种更优雅的转义动态 SQL 的方式? [英] A more elegant way of escaping dynamic 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!

第二个操作 将是 REPLACE(@job_code, '''', '''''') ，我在其中转义任何单引号可能存在于该领域.

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

难道没有更优雅的方式来编写这行代码吗?

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

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

推荐答案

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

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

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

这篇关于一种更优雅的转义动态 SQL 的方式?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！