t-sql 2012 update statment

Question

t-sql 2012 update statment

dianerstein 8713

SSCrazy

Points: 2197
More actions
November 28, 2016 at 3:15 pm

#319342

In a sql server 2012 database, I have a called that is declared as varchar(max) where the column is called stringtemplate.
I want to update the value in the column with the value listed below, however the update statement does not work. There is a problem with the set statement. The double quotes or single quotes around the entire value does not stay.
Thus can you tell me what I can do to make the following sql work:
update a
set stringtemplate = '
&CUR_DATE.EVAL
 
 
 


To the &PAR_NAME.EVAL of &STU_FNAME.EVAL &STU_LNAME.EVAL &PERMNUM

&PAR_ADDR.EVAL
&PAR_CITY.EVAL, &PAR_STATE.EVAL &PAR_ZIP.EVAL 
 
 
 
Dear &PAR_NAME.EVAL and &STU_FNAME.EVAL &STU_LNAME.EVAL: 
 
 
This letter is to inform you that &STU_FNAME.EVAL has the equivalent of 5 or more unexcused absences this school year. We believe student attendance in school is a key component to school success, so it is very important for all children to develop habits of good attendance. Poor attendance contributes to failing grades, decreased learning opportunities, lower academic achievement and may limit your child's opportunities to be involved with school activities. 
 
 
(Option 1)
Please call me at PHONE NUMBER to discuss these absences. During this phone conference we will address concerns and issues that may be contributing to the absences, and develop a collaborative plan to try to improve attendance. 
 
(Option 2)
I have scheduled an attendance review on DATE at TIME. We will meet at PLACE located at ADDRESS. Please check in at the main office upon your arrival. During this meeting we will address concerns and issues that may be contributing to the absences, and develop a collaborative plan to try to improve attendance. The student and parent should attend this review. If you cannot attend this meeting please contact me at PHONE #. 
 


We are notifying you so that together we can address all issues that may be contributing to these absences. Communication between the home and school is very important and we request that parents call the school each and every day that a student is going to be absent. Let us work as partners to make your child successful in school.http://www.sqlservercentral.com/Forums/Skins/Classic/Images/RichTextBoxTable/tbl_bottomleft.gif
 
Sincerely, 
 
 
&SPA_NAME.EVAL 

School Support Liaison/ Attendance Designee
&SCHOOLNAME.EVAL'
from test.dbo.customer a
where a.custid=6789

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

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

drew.allen SSC Guru Points: 76944 More actions · Answer 1

Your text contains a single quote that hasn't been properly escaped. You need to use two single quotes in your text to get one single quote, so child's should be child''s (that's two single quotes, not one double quote).

There is another option, but I think it's ugly, so I'll leave it to other people to suggest it.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 2

See the .WRITE syntax here:

https://msdn.microsoft.com/en-us/library/ms177523.aspx

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

wendy elizabeth SSCoach Points: 16915 More actions · Answer 3

The .write statement is used was an nvarchar(max) column. I am using a varchar(max) column. Will the write statement work with a varchar(max) column?

Also if you have a chance, would you give me an example of how to use the .write statement?

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 4

wendy elizabeth (11/29/2016)
The .write statement is used was an nvarchar(max) column. I am using a varchar(max) column. Will the write statement work with a varchar(max) column?
Also if you have a chance, would you give me an example of how to use the .write statement?

Did you reference the link I provided in my reply? Apparently not. I suggest you do so.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service