I've done some research but cannot find a solution to my dilemma
I have this update statement that is getting halted by fields that contain single quotes.
Thank you for any advice!!
UPDATE SHITEM SET SHITEM.FPARTNO = 'FAS56075', SHITEM.FREV = '', SHITEM.FMDESCRIPT = 'BOLT 1/4-20X.50 MACH' PAN PHIL ST ZP' WHERE SHITEM.FPARTNO = '05-00016-005' AND SHITEM.FREV = '' AND SHITEM.FMDESCRIPT LIKE '1/4-20X1/2 PHILIPS PAN STEEL'
UPDATE SHITEM SET SHITEM.FPARTNO = 'FAS56076', SHITEM.FREV = '', SHITEM.FMDESCRIPT = 'INSERT NUT ST 1/4-20 UNC-1 SHANK' WHERE SHITEM.FPARTNO = '04-73484' AND SHITEM.FREV = '' AND SHITEM.FMDESCRIPT LIKE 'NUT INSERT 1/4-20 UNC -1 SHAN'
UPDATE SHITEM SET SHITEM.FPARTNO = 'INS56077', SHITEM.FREV = 'A00', SHITEM.FMDESCRIPT = 'INS GLASTIC GPO3 12 400A' WHERE SHITEM.FPARTNO = '986-00001-00' AND SHITEM.FREV = 'A00' AND SHITEM.FMDESCRIPT LIKE '400A GRAY GPO3 12' PULTRUSION '
UPDATE SHITEM SET SHITEM.FPARTNO = 'BUS56078', SHITEM.FREV = '', SHITEM.FMDESCRIPT = 'BUS PWR LV 300A BUSWAY' WHERE SHITEM.FPARTNO = '04-70432-145' AND SHITEM.FREV = '' AND SHITEM.FMDESCRIPT LIKE 'COPPER BAR 1/4 X 1-1/4 x 145 SILVE'
UPDATE SHITEM SET SHITEM.FPARTNO = 'FAS56075', SHITEM.FREV = '', SHITEM.FMDESCRIPT = 'BOLT 1/4-20X.50 MACH'' PAN PHIL ST ZP' WHERE SHITEM.FPARTNO = '05-00016-005' AND SHITEM.FREV = '' AND SHITEM.FMDESCRIPT LIKE '1/4-20X1/2 PHILIPS PAN STEEL'
What is the exact text of the "truncating error"? Usually you see the error message "string or binary data will be truncated" when the length of the string you are trying to store is greater than the max width of the column.
For example, if the column SHITEM.FMDESCRIPT is of type VARCHAR(32) and the string you are trying to store is longer than 32 characters, you will see this error.
Having a space in and of itself in a string should not cause any error. As @gbritton had suggested, if you have single quotes in the string, you need to escape them with another single quote.
Sounds like you are generating Dynamic SQL from user data, and then executing it? If so you are at huge risk from SQL Injection. What would happen, for example, if for SHITEM.FMDESCRIPT instead of the value of
BOLT 1/4-20X.50 MACH' PAN PHIL ST ZP
the value was
XXX';DELETE SHITEM;'--
its going to truncate your SHITEM table
Use a parameterised query instead. That won't prevent the attempt to store to wide a value in a narrow column, but it will stop SQL injection, and performance will be much better (in general terms; individual Row-By-Agonising-Row UPDATEs like this won't be improved, but other queries that are encountered regularly in the APP will run faster.
A parameterised query will also take care of your single/double quote problem (i.e. if your "value" is contained within a variable, and passed as a variable, rather than being concatenated into a Dynamic SQL command string)
Should I research converting my current statements:
UPDATE ITEM SET ITEM.PARTNO = 'FAS56075', ITEM.PARTREV = '', ITEM.DESC= 'BOLT 1/4-20'X.50' MACH PAN PHIL ST ZP'
WHERE ITEM.PARTNO = '05-00016-005' AND ITEM.PARTREV = '' AND ITEM.DESC = '1/4-20'X1/2 PHILIPS PAN STEEL'
If this were an update through an application I would agree about parameterization however if this is an ad-hoc update using a simple update query without variables is fine.
Try:
UPDATE ITEM SET PARTNO = 'FAS56075'
, PARTREV = ''
, DESC = 'BOLT 1/4=20'' X .50'' MACH PAN PHIL ST ZP'
WHERE PARTNO = '05-00016-005'
AND PARTREV = ''
AND DESC = '1/4-20''X1/2 PHILIPS PAN STEEL';
Depends :). Generally the Parameter would get its content from somewhere else - e.g. the call to an SProc. Ideally the CALL / EXEC would be constructed, from the APP, using a local variable of appropriate datatype - e.g. if programming in C or VB whatever the variable would be string in this case, but could be, say, Date in another case (date being particularly thorny for converting to something "safe and unambiguous" for SQL to parse), and then the whole "convert data to something suitable for SQL" doesn't apply - the language will transfer the variable's data content in a format suitable for SQL - I suppose the ODBC driver, or whatever, takes care of that. Using that style of programming, and parameterization of the query, also avoids issues with SQL Injection and (not really applicable in this case) means that the Query plan gets cached and reused for identical queries (i.e. where the query is identical because is contains parameters, where using dynamic SQL, i.e. which contains the value for the parameters instead, never produces an "identical" query, and is thus much harder for SQL to reuse an existing/cached query plan.
Kristen, I see, you're referring to assigning variables in an application which is passing them to a stored procedure in SQL Server. In that case using stored procedures or other methods of parameterizing increases efficiency in using cached or pre-compiled query plans and protects from SQL injection attacks. I completely agree, these are best practices. The OP never responded however it looks as though they were looking to run an ad-hoc update so I was just showing how that can be done as simply as possible. In SSMS declaring T-SQL variables for this would just add another level of complexity as you would still have to escape single quotes or alternatively,
SET QUOTED_IDENTIFIER OFF
GO
UPDATE ITEM SET PARTNO = "FAS56075"
, PARTREV = ''
, DESC = "BOLT 1/4=20' X .50' MACH PAN PHIL ST ZP"
WHERE PARTNO = "05-00016-005"
AND PARTREV = ""
AND DESC = "1/4-20'X1/2 PHILIPS PAN STEEL";
In this case delimiting strings with double quotes that contain single quotes.
Indeed, I mechanically generate SQL statements in just that manner.
it was not clear to me if the handful of examples posted were samples of individual EXECs, or part of a mammoth one-time (mechanically generated) modification of the data. if the latter then I agree that generating the dynamic SQL with doubled-up-quotes would do fine.
If it is a often used process, even if mechanically generated, it might be worth parameterizing it - to get cache reuse - but for single-row updates I doubt there is any gain, in particular if [Unique] Clustered Index keys are provided in the WHERE clause. SSMS uses a "Declare @Var, Assign a string to it, now use that in an SProc call" if you use Table Designer to add a Comment to a Column (or table etc.). I've never understood why they bother in that case, as a string parameter to the Sproc would seem easier. One possible advantage [given the codes uses an SProc EXEC statement, rather than a plain UPDATE] is that the @Variable assignment can easily be changed to a formula of some sort, whereas a parameter to an SProc doesn't allow e.g. string manipulation. but that's a pretty obscure edge-case and getting away from the point ... I agree that if this is a one-off script, mechanically generated from existing data, then double-the-quotes would be best; if it is actually multiple, individual, calls from an APP or somesuch then I think parameterized would be better (and, in that case, here, we would write go further and create an Sproc to encapsulate the process and the @Parameters)
.