Try to enclose ' within SQL Output

Question

Post reply

Try to enclose ' within SQL Output

Welsh Corgi

SSC Guru

Points: 116520
More actions
April 9, 2012 at 6:54 am

#258903

I'm trying to get the following output from the SQL Statement listed below, however I forgot how to handle the Quote '.
UPDATE ctl.Accounts_Payable SET insert_date = '2012-04-04'
I tried several variations but I did not get it right:
DECLARE @InsertDate VARCHAR(12)
SET @InsertDate = '2012-04-04'
SELECT 'UPDATE ' + s.Name + '.' +
t.name
+ ' SET insert_date = ' + @InsertDate
I get the following from the code listed above:
UPDATE ctl.Accounts_Payable SET insert_date = 2012-04-04
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Steve Cullen SSCertifiable Points: 5598 More actions · Answer 1

Try :

+ ' SET insert_date = ''' + @InsertDate + ''''

Converting oxygen into carbon dioxide, since 1955.

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 2

As an alternative...

+ ' SET insert_date = ' + QUOTENAME(@InsertDate,'''')

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

R.P.Rozema SSChampion Points: 12317 More actions · Answer 3

Jeff Moden (4/9/2012)
As an alternative...
+ ' SET insert_date = ' + QUOTENAME(@InsertDate,'''')

Be warned though that the parameter and return type of quotename() is sysname, i.e. any value you put in will -if it is longer than 128 characters- be truncated at 128 characters. It will do so silently, i.e. no truiincation warning or whatever will show. In this case, where the input is at most 12 characters long, the use of quotename() is perfectly valid.

Posting Data Etiquette - Jeff Moden[/url]
Posting Performance Based Questions - Gail Shaw[/url]
Hidden RBAR - Jeff Moden[/url]
Cross Tabs and Pivots - Jeff Moden[/url]
Catch-all queries - Gail Shaw[/url]

If you don't have time to do it right, when will you have time to do it over?

R.P.Rozema SSChampion Points: 12317 More actions · Answer 4

Let me correct myself: in sql 2005 and earlier the value would be truncated, from 2008 the function will return null if the input is over 128 characters loing.