SQLTeam.com | Weblogs | Forums

T-sql 2012 update statement


#1

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.


Sincerely,


&SPA_NAME.EVAL

School Support Liaison/ Attendance Designee

&SCHOOLNAME.EVAL

' from test.dbo.customer a where a.custid=6789

#2

When you say [quote="jassie1, post:1, topic:8580"]
the update statement does not work
[/quote]

Do you mean that you get a syntax error or that the statement executes but the target row is not updated.[quote="jassie1, post:1, topic:8580"]
The double quotes or single quotes around the entire value does not stay.
[/quote]

Well no, it would not! Think about it for any other language you know. e.g. in C# if I write:

var mystring = "this is my string";

The quotes are not preserved as part of the string. Is that what you want? If so, you need to add extras like this:

set stringtemplate = ' '' <text> '' '

which is, one single quote, a space, two single quotes, some text (like your letter), two more single quotes,a space and a closing single quote.


#3

I am getting the error message, "Msg 102, Level 15, State 1, Line 1 Incorrect syntax near 's'."

When I did as you suggested "The quotes are not preserved as part of the string. Is that what you want? If so, you need to add extras like this:
set stringtemplate = ' '' '' '
which is, one single quote, a space, two single quotes, some text (like your letter), two more single quotes,a space and a closing single quote."

I tried to use the value as a parameter value and that did not make any difference. What do you suggest that I try?


#4

I see your problem now:

may limit your child's

you need to double up the single quotes here (and anywhere else you want it to appear) like this:

may limit your child''s