|
|
|
Hall of Fame
       
Group: General Forum Members
Last Login: 2 days ago @ 1:16 PM
Points: 3,367,
Visits: 1,567
|
|
|
|
|
|
Forum Newbie
      
Group: General Forum Members
Last Login: Friday, May 17, 2013 11:54 PM
Points: 2,
Visits: 164
|
|
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
|
|
|
|
|
Forum Newbie
      
Group: General Forum Members
Last Login: Sunday, January 06, 2013 11:06 PM
Points: 1,
Visits: 4
|
|
Answer is
DECLARE @topsql nvarchar(200)
SET @topsql = 'DECLARE @quotedvar nvarchar(100) ' + char(13) + 'DECLARE @sql nvarchar(1000) ' + char(13) + 'SET @quotedvar = ''O''''Neil''' + char(13) +
'SET @sql = ''PRINT '''''' + REPLACE(@quotedvar,'''''''','''''''''''') + '''''''''+ char(13) + 'PRINT @sql ' + char(13) + 'EXEC sp_executesql @sql '
PRINT @topsql PRINT '-------'
EXEC sp_executesql @topsql
Finger Crossed 
Vandana
|
|
|
|
|
Forum Newbie
      
Group: General Forum Members
Last Login: Friday, May 17, 2013 5:05 PM
Points: 3,
Visits: 83
|
|
Following code will do the trick;
DECLARE @topsql nvarchar(200)
SET @topsql = 'DECLARE @quotedvar nvarchar(100) ' + char(13) + 'DECLARE @sql nvarchar(1000) ' + char(13) + 'SET @quotedvar = ''O''''Neil'' ' + char(13) + 'SET @sql = ''PRINT '''''' + REPLACE(@quotedvar,'''''''','''''''''''') + '''''''' ' + char(13) + 'PRINT @sql ' + char(13) + 'EXEC sp_executesql @sql '
PRINT @topsql PRINT '-------'
EXEC sp_executesql @topsql Thanks, Hasham Niaz
|
|
|
|
|
Ten Centuries
      
Group: General Forum Members
Last Login: Yesterday @ 3:05 AM
Points: 1,400,
Visits: 6,896
|
|
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.
BrainDonor Linkedin
|
|
|
|
|
Forum Newbie
      
Group: General Forum Members
Last Login: Wednesday, March 27, 2013 7:39 AM
Points: 9,
Visits: 44
|
|
Nice, if that method can be applied cleanly and consistently across all SPs and all apps.
Unfortunately, many of us work in sloppy production environments with a number of development people with differing abilities, working on different apps, web pages and SPs with different coding standards. In that kind of situation, it's all too easy for folks to forget the O'Neil scenarios and forget to code defensively in both web code and SPs.
On the basis that prevention is better than a painful cure, or just belt-and-braces, I have a background SQL Agent task that runs every night on the "usual suspects" (Surname and address columns) to replace single apostrophes (CHAR(39)) with a Grave accent (CHAR(96))
So, O'Neil becomes O`Neil.
I appreciate this may offend some people, because it should not be necessary, but it does help a little to prevent public-facing apps from failing in an embarrasing way.
|
|
|
|
|
Ten Centuries
      
Group: General Forum Members
Last Login: Yesterday @ 4:24 AM
Points: 1,026,
Visits: 752
|
|
How about using Quotename...?
SELECT QUOTENAME('o''neil', '''')
|
|
|
|
|
Grasshopper
      
Group: General Forum Members
Last Login: Yesterday @ 10:22 AM
Points: 10,
Visits: 586
|
|
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
+1
|
|
|
|
|
SSCommitted
      
Group: General Forum Members
Last Login: Yesterday @ 6:17 AM
Points: 1,625,
Visits: 1,926
|
|
johnbrown105 56149 (1/3/2013)
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+1
+2
In addition, I try to use Powershell Here-Strings wherever I can instead of dynamic SQL.
MG
"There are two ways of constructing a software design. One way is to make it so simple that there are obviously no deficiencies. And the other way is to make it so complicated that there are no obvious deficiencies." Tony Hoare
"If you think it's expensive to hire a professional to do the job, wait until you hire an amateur." Red Adair.
|
|
|
|
|
Old Hand
      
Group: General Forum Members
Last Login: 2 days ago @ 7:11 AM
Points: 317,
Visits: 619
|
|
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.
|
|
|
|