MG-148046 (1/3/2013)

---

+2

In addition, I try to use Powershell Here-Strings wherever I can instead of dynamic SQL.

REPLACE(@quotedvar, CHAR(39), CHAR(39)+CHAR(39))

RichB (1/3/2013)

---

How about using Quotename...?

SELECT QUOTENAME('o''neil', '''')

BrainDonor (1/3/2013)

---

I have frequently had to script out Stored Procs that use dynamic SQL, and the joys of altering the number of quotes defies description.
Using the 'Generate Scripts' option within SSMS is a useful solution for such situations and a lot of people don't realise that it can be used for such. It will create a script with the correct number of quotes, providing an easy way to script such things for moving to different databases.

roger.plowman (1/3/2013)

---

This issue is yet another example of why SQL is one of the worst-designed languages of all time from a syntactic POV.

Would it have killed the designers to create two string delimiters that could be interchanged (ala BASIC) and reserved square brackets for field/table delimiting? And while we're at it to use #'s to delimit dates/times (ala MS Access)?

Oh, and use a dedicated "escape" character instead of doubling the escaped character? Sheesh!

Sorry, this is one of (many) pet peeves I have with T-SQL.

Michael R. OBrien Jr (1/2/2013)

---

I guess I have never been a fan of triple quoting, I usually use CHAR(39) I find it is a lot easier to read for others:

SELECT 'O' + CHAR(39) + 'Neal'

Just a thought, nice article though

SET @sql = 'Print ' + CHAR(39) + REPLACE(@quotedvar, CHAR(39), CHAR(39) + CHAR(39)) + CHAR(39)

RichB (1/3/2013)

---

How about using Quotename...?

SELECT QUOTENAME('o''neil', '''')

pop022 (1/3/2013)

---

I know the point you were trying to get across, but using sp_executesql is not needed to print the value of the variable. It could simply be written as follows.

DECLARE @quotedvar nvarchar(100)

SET @quotedvar = 'O''Neil'

PRINT @quotedvar

Kenneth.Fisher (1/3/2013)

---

I looked at Quotename when I was writing this but honestly I'm still not sure exactly what it does. I tried it out in this context and couldn't get it to do what I needed it to. I may just have been using it wrong though.