15

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.

Mike Perrenoud
  • 66,820
  • 29
  • 157
  • 232
  • Have you considered passing directly as a variable to your dynamic SQL instead of concatenating it? I know going with the hardcoded value is better for avoiding parameter sniffing issues, but depending on the workload this might be a simpler option. – Eric J. Price Apr 30 '13 at 20:17
  • @Love2Learn, it's not something I've considered -but after thinking about it I'm pretty sure I want to shy away from it because I want to keep the stored procedure pristine so that it can be used by any consumer. Adding that type of assumption would make it more difficult to keep it consumable. – Mike Perrenoud Apr 30 '13 at 20:18
  • 3
    Using a variable for `'` will make it slightly more readable, but its not a massive improvement. – Jon Egerton Apr 30 '13 at 20:23
  • Can you explain "used by any consumer"? I would argue that passing it as a parameter to your dynamic SQL could be considered more consumable since as long as you can get it into a datatype that can be passed to a dynamicSQL statment you can use it without worrying about escaping anything. – Eric J. Price Apr 30 '13 at 20:23
  • @Love2Learn, so what I'm saying is that if I'm forcing the consumer to escape the data -that's at best quite different from how you might consider consuming this procedure. Or any other procedure for that matter. You see a procedure, pass it the values, and expect it to manipulate the data. – Mike Perrenoud Apr 30 '13 at 20:25
  • 1
    Looks like I wasn't explaining myself correctly. The `sp_executeSQL` solution is the one I was trying to suggest. ;) – Eric J. Price Apr 30 '13 at 20:31

4 Answers4

28

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;
EricZ
  • 6,065
  • 1
  • 30
  • 30
  • Now this is an interesting option -because it handles a couple interesting scenarios. I can write my SQL to handle `null` values much easier this way, there are less branches when it comes to `if` statements, and at the same time I don't have to escape anything. Interesting my friend! – Mike Perrenoud Apr 30 '13 at 20:30
  • 2
    +1: This is the solution I was picturing and the nice thing about it is you can populate @job_code anyway you want without worrying about any escaping. – Eric J. Price Apr 30 '13 at 20:30
  • 4
    Added bonuses are you get plan reuse and less plan cache bloat. – StrayCatDBA May 01 '13 at 02:42
6

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.

Atario
  • 1,371
  • 13
  • 24
2

You could declare constants:

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

SET @where = 'job_code = ' + @SQ + REPLACE(@job_code, @SQ, @SQ + @SQ) + @SQ
Joe Enos
  • 39,478
  • 11
  • 80
  • 136
  • +1 for a solution I had not yet considered. I'm not sure I want to do it that way, as I'm not sure it's any better (**personally speaking**) than the one I'm using now -but a +1 nonetheless! – Mike Perrenoud Apr 30 '13 at 20:23
  • Yep, I don't know if I'd do it either - still not all that easy to read, but it's an option. – Joe Enos Apr 30 '13 at 20:24
  • Sorry if I seem harsh, but this is lipstick on a pig. ErikZ's answer is the cleanest solution and is the way that MS intend dynamic SQL to be generated, even back in SQL Server 2000. http://msdn.microsoft.com/en-us/library/aa172445%28v=sql.80%29.aspx – DeanOC May 09 '13 at 21:52
  • @DeanOC Yep, that's why EricZ's answer got 23 upvotes including one from me - it's clearly the best answer for many dynamic SQL scenarios. – Joe Enos May 09 '13 at 22:35
2

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)
Joe Enos
  • 39,478
  • 11
  • 80
  • 136