A more elegant way of escaping dynamic SQL?

Question

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.

Answer 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;