Daily WTF - Deleting SQL Data (or Properly Formatting SQL)

We've been having an issue with one of our database tables' mysteriously deleting data at random. We could have *swore* it was some sort of SQL intrusion or code bug. We leaned towards SQL intrusion because this database has been in use for quite some time. However, that was not the case. A new Stored Procedure that touched this table was added recently. In quick review, it didn't appear that anything was wrong. Here it is:

ALTER PROCEDURE [dbo].[ProcedureName]
@EmailAddress varchar(256)
AS
IF EXISTS (SELECT TableID from The_Table_Name_Was_Pretty_Long
WHERE EmailAddress=@EmailAddress)
BEGIN
         DELETE The_Table_Name_Was_Pretty_Long
         WHERE @EmailAddress = @EmailAddress
END
Lesson Learned. :-)

It's kind of embarrasing that I've reviewed this statement at least 2 dozen times in the past 3 days and couldn't figure out what the issue was.

Published Thursday, April 12, 2007 10:54 AM by Jason N. Gaylord
Filed under:

Comments

# re: Daily WTF - Deleting SQL Data (or Properly Formatting SQL)

Thursday, April 12, 2007 11:33 AM by Richard

Been there, done that, coding it tomorrow!

# re: Daily WTF - Deleting SQL Data (or Properly Formatting SQL)

Thursday, April 12, 2007 11:38 AM by Jason N. Gaylord

:-) - Be sure to kick me at http://www.dotnetkicks.com/database/Daily_WTF_Deleting_SQL_Data_or_Properly_Formatting_SQL

# re: Daily WTF - Deleting SQL Data (or Properly Formatting SQL)

Thursday, April 12, 2007 12:15 PM by Palermo4

Those where clauses!  Hey thanks for sharing the war-story.

# re: Daily WTF - Deleting SQL Data (or Properly Formatting SQL)

Thursday, April 12, 2007 2:10 PM by Jason N. Gaylord

Here's a great post from Rachel...

http://www.rachelappel.com/2007/04/12/WhenHumansAndMachinesArentQuiteOnTheSamePage.aspx

# re: Daily WTF - Deleting SQL Data (or Properly Formatting SQL)

Thursday, April 12, 2007 4:14 PM by SqlMenace

This is somewhat similar

run this in the pubs db

select * from prodorders po

join products p on p.id = p.id

instead of doing this

select * from prodorders po

join products p on p.id = po.id

See? p.id = p.id instead of p.id = po.id

Denis

# re: Daily WTF - Deleting SQL Data (or Properly Formatting SQL)

Friday, April 13, 2007 12:01 AM by Ernesto

Sorry about my English, but I noted that the WHERE in the DELETE clause was wrong.

Bad:

WHERE @EmailAddress = @EmailAddress

Ok:

WHERE EmailAddress = @EmailAddress

# re: Daily WTF - Deleting SQL Data (or Properly Formatting SQL)

Friday, April 13, 2007 3:11 AM by Chris

Yep i've done that too. Comes from copying/pasting the parameter instead of typing manually. (To avoid typos).

# re: Daily WTF - Deleting SQL Data (or Properly Formatting SQL)

Friday, March 26, 2010 7:32 PM by Summer

Hi. It's so much easier to write a resume than to craft a spirit. Help me! I can not find sites on the: Baby girl nursery bedding. I found only this - <a href="baby-bedding.net/">creme brulee baby bedding</a>. Bedding, life your objective, and forever persist when you are many or natural. We are being fitted by operations because of our untranslated suite, bedding. With respect :confused:, Summer from Wales.

Leave a Comment

(required) 
(required) 
(optional)
(required)