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!

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

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.

回答1:

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;

回答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:

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.

回答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)