Using Case Statement in Delete query

• Fiddler

  SSC Veteran

  Points: 233

  More actions

  December 17, 2008 at 2:40 pm

  #197610

  Hello,

  I am not very facile with case statements in SQL. I've been instructed to write a select query that puts code in a temp table, then to delete records based on criteria. He's wanting to test running times. I had already built the same query with the case statement in the WHERE clause of the SELECT statement and it returns 73 rows for my test case.

  I put the same criteria in a case statement for my DELETE query using NOT, but no records at all are returned, although it does compile. Can you tell me why?

  Here's the code:

  DELETE FROM #TmpProgressNotes WHERE NOT

  1= Case

  WHEN @ServiceCodeGroup = 'IPC Summary' AND (ServiceCodeGrpDesc = 'Therapy' OR ServiceCodeGrpDesc = 'Med Management' OR ServiceCodeGrpDesc = 'Skills') THEN 1

  ELSE 0

  END

  AND

  1=CASE

  WHEN @ReportOption = 'By Program' and @ProgramOrCampusOrClient = 'Outpatient - CFH'

  AND (TFSProgramInNote = 'Outpatient - CFH' OR TFSProgramInNote = 'Outpatient')AND CampusInNote = 'CFH'THEN 1

  WHEN @ReportOption = 'By Program' AND @ProgramOrCampusOrClient = 'Outpatient - WCH'

  AND (TFSProgramInNote = 'Outpatient - WCH' OR TFSProgramInNote = 'Outpatient')AND CampusInNote = 'WCH' THEN 1

  WHEN @ReportOption = 'By Program' AND @ProgramOrCampusOrClient <> 'Outpatient - CFH' AND @ProgramOrCampusOrClient <> 'Outpatient - WCH'

  AND TFSProgramInNote = @ProgramOrCampusOrClient THEN 1

  WHEN @ReportOption = 'By Campus' AND CampusInNote = @ProgramOrCampusOrClient THEN 1

  WHEN @ReportOption = 'By Client' AND V.TFSClientNumber = @ProgramOrCampusOrClient THEN 1

  WHEN @ReportOption = 'By Signator'

  AND (DocSignedNm LIKE '%' + RTRIM(@ProgramOrCampusOrClient) + '%') THEN 1

  ELSE 0

  END

• Garadin

  One Orange Chip

  Points: 29613

  More actions

  December 21, 2008 at 9:44 am

  #915869

  Have you run it with NOT as a select statement to see what is returned?

  How many records are in your test data set?

  The logic of 1 = case ... then 1 else 0 end... is OK, so it's something in your data that we can't see.

  Since it is test data, select from your current temp table to a new temp table (table variables are even easier to use here) and test different combinations of your case statement with a logic flow such as:

  DECLARE @A Table(

  fields...)

  INSERT INTO @A(fields...)

  SELECT * FROM #TmpProgressNotes

  DELETE FROM @A

  WHERE 1 = CASE WHEN ...

  SELECT * FROM @A

  This will allow you to comment out pieces of your case statement on the fly and re-run with almost no effort to narrow down where your problem is.

  Seth Phelabaum

  ---

  Consistency is only a virtue if you're not a screwup.

  Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

• RBarryYoung

  SSC Guru

  Points: 143329

  More actions

  December 21, 2008 at 10:25 am

  #915872

  Fiddler (12/17/2008)

  ---

  I put the same criteria in a case statement for my DELETE query using NOT, but no records at all are returned, although it does compile. Can you tell me why?

  Two things: First, the way that you added the "NOT" makes it only apply to the first CASE. If you want to negate the whole logical condition of your WHERE clause you have to wrap the whole thing in parenthesis: WHERE NOT ( {everything else} )

  Secondly, I am not sure if putting NOT in is the right thing at all. Do realize that that should then Delete everything except the 73 rows that your SELECT returned? Is that what you want to do?

  ...He's wanting to test running times....

  What you've written here is just a CASE modification of the old WHERE ...

  AND ((@ParX IS NULL) OR (ColX = ParX))

  AND ((@ParY IS NULL) OR (ColY = ParY))

  ... trick, which performs poorly because it cannot effectively use any indexes and (at best) is forced to scan the whole table.

  If scanning the whole table will not be fast enough for you, then you will need to leverage you indexes and the most practical way to do that for your example would be to break your query into seperate SQL statements and then use IF's to control which one was executed. Ideally, these separate queries should be in separate stored procedures (one query per procedure) to help avoid parameter-sniffing problems.

  [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
  Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

• J-440512

  SSCertifiable

  Points: 6285

  More actions

  December 22, 2008 at 10:29 am

  #916252

  -> R Barry Young

  because it cannot effectively use any indexes and (at best) is forced to scan the whole table.

  I am not clear as to why indexes cannot be used, if there are any on the columns named.

  Would you please elaborate a bit more ?

  Regards

• RBarryYoung

  SSC Guru

  Points: 143329

  More actions

  December 22, 2008 at 12:16 pm

  #916310

  Because which columns/indexes would be relevant changes all the time, the compiler cannot determine them ahead of time. If you split them into separate queries or even procedures, then the compiler can optimize them just for the specific conditions under which that query or procedure would execute.

  [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
  Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]