February 7, 2006 at 8:26 pm
!!!URGENT!!!
I am writing a Dynamic piece of T-SQL inside a cursor so that I can remove duplicate records from a table (see article: http://www.sql-server-performance.com/dv_delete_duplicates.asp for aguide to what i am doing)
Here is the code to build the Delete Statement:
-- now build the rowcount and delete statements.
SELECT @nvchCommand = N'SET ROWCOUNT ' + @chCount +
'DELETE <TABLENAME>' +
' WHERE FirstName = ' + CHAR(39) + @vchFirstName + CHAR(39) +
' AND LastName = ' + CHAR (39) + @vchLastName + CHAR(39) +
' AND AddressLine1 = ' + CHAR(39) + @vchAddressLine1 + CHAR(39) +
' AND Postcode = ' + CHAR(39) + @vchPostcode + CHAR(39)
The problem arises on the execute statement when there is an apostrophe in either the firstname, lastname or addressline1 fields.
What would be the best way to deal with this.
Thanks
February 7, 2006 at 9:06 pm
read in BOL article about sp_executesql and using variables in dynamic SQL.
_____________
Code for TallyGenerator
February 9, 2006 at 6:36 am
Hi,
I think this should work:
You can replace this: CHAR(39) + @vchFirstName + CHAR(39)
with this: CHAR(39) + REPLACE(@vchFirstName,char(39),char(39)+' + char(39) + '+char(39)) + CHAR(39)
Follow my example (you can execute it on Query Analyzer"):
declare @test-2 varchar(30)
declare @select varchar(100)
select @test-2 = 'Henrique' + char(39) + 's guitar'
select @select = char(39) + REPLACE(@test,char(39),char(39)+' + char(39) + '+char(39)) + char(39)
select @Select
Best Regards,
Henrique.
February 9, 2006 at 12:41 pm
The general rule is to use an apostrophe is to embed to apostrophes for one. Thus the following is correct:
select * from oname
where lname = 'O''BRIEN'
When assembling SQL from variables, use the replace command to acheive the double apostrophe like the following:
select @cmd = 'select * from oname ' +
'where lname = ''' + replace (@nam,char(39),char(39)+char(39)) +''''
Steve
February 12, 2006 at 4:49 pm
February 12, 2006 at 8:59 pm
Yeah, but, good Lord... don't use a cursor to find and delete dupes (or for anything else for that matter)... here's a fully commented template for deleting dupes... you could change it to dynamic SQL if you want... (I can't believe they allowed a cursor to be published in a performance oriented site like the link you gave)...
CREATE PROCEDURE dbo.DupeDeltablename AS
/*==================================================================================================
Purpose: Duplicate detection and deletion (boilerplate)
Author: Jeffrey B. Moden
Revisions:
Rev 00 - 31 May 1996 - Jeff Moden - Initial Release
Rev 01 - 11 Feb 2000 - Jeff Moden - Speed Improvements for very large tables by using DISTINCT
Rev 02 - 14 Feb 2000 - Jeff Moden - Speed Improvements for very large tables removing concatenization
Rev 03 - 18 Feb 2000 - Jeff Moden - 1. Add comments for release as public template
2. Add generic "CREATE PROCEDURE" for release as public template.
Customization Instructions:
1. Replace "tablename" with name of table to do dupe-checks in.
2. Replace dupchkfield1 with first field name of key fields that determine what a "dupe" is.
3. Replace dupchkfield2 with second field name of key fields that determine what a "dupe" is.
4. Add additional "dupchkfield"s as necessary using "AND" as in the remarks in the code.
5. Replace "chronologyfield" with the name of the field that determines the age of the record. This
may be an auto-incrementing IDENTITY field or a date/datetime/time field.
Usage:
EXEC DupeDeltablename
where "tablename" is the name of the table to be checked for dupes.
Notes:
1. Note that the "CREATE PROCEDURE dbo.DupeDeltablename AS" will become...
"CREATE PROCEDURE dbo.DupeDelCUSTOMER AS" if the table name used in Step 1 above is "CUSTOMER".
2. All comments may be removed with impunity.
==================================================================================================*/
DELETE
FROM tablename
WHERE tablename.ID IN
--The following SELECT statement acts as a derived table. The DISTINCT term is
--required because if there are 3 or more dupes for any given fields, the return
--will grow, exponentially.
(
SELECT DISTINCT t1.ID
FROM tablename AS t1
INNER JOIN tablename AS t2
-- These "ON" conditions contain the comparisons of the fields to be checked for dupes
ON t1.dupchkfield1 = t2.dupchkfield1
AND t1.dupchkfield2 = t2.dupchkfield2
--AND t1.dupchkfield3 = t2.dupchkfield3 --Add additional fields like this
--AND t1.dupchkfield4 = t2.dupchkfield4 --Add additional fields like this
-- This "ON" condition contains the comparison of the field to be checked for age.
-- "Age" can be based on date/datetime/time or auto-incrementing ID.
-- In all cases, the earlier/lower the value, the older the record is compared to others.
-- The comparison operator (">" in this example) determines which duplicate will be kept.
-- The functionality of this example can be changed by changing the comparison operator to...
-- ">" KEEPS only the OLDEST or EARLIEST record if duplicates are detected (OLD IS GREATer)
-- "<" KEEPS only the YOUNGEST or LATEST record if duplicates are detected (YOUNG is LESS-THAN OLD)
-- "<>" REMOVES ALL DUPLICATED RECORDS INCLUDING YOUNGEST AND OLDEST (be careful)
AND t1.chronologyfield > t2.chronologyfield
)
--Jeff Moden
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply