1

I am trying to debug a stored procedure that I did not write. Specifically focused on the third line beginning with AND (Citation_Main.User_Defined5, if I set a subset of records' User_Defined5 column to NULL, using an update query, then the stored procedure writes an export file that I am expecting. However, if the User_Defined5 field is set to 'N', a file is not written. 

DECLARE Cit_Select_Cursor CURSOR FOR SELECT Citation_Main.Number,Citation_Main.Issue_Date,
        Citation_Main.Issue_Time,Citation_Main.Officer_Comment1, Citation_Main.Officer_ID,
        Citation_Main.Officer_Name,Citation_Main.License_Plate,Citation_Main.License_State,
        Citation_Main.Location_Block,Citation_Main.Location_Street,Citation_Main.Location_Dir,
        Citation_Main.Location_Suffix,Citation_Main.Amount_Due,Citation_Main.Meter,
        Citation_Main.Make,Citation_Main.Color_Major,Citation_Main.Body_Style,
        Citation_Main.License_Type,Citation_Main.VIN,Citation_Activity.ChargeCode,Citation_Activity.DebitAmount
        ,Citation_Main.Officer_Comment2
        FROM Citation_Main --TTM_Bak
--ADDED 03/12/2016 Inner Join and check for void and warning
--      INNER JOIN Citation_Main On TTM_Bak.Number = Citation_Main.Number
        INNER JOIN Citation_Activity On Citation_Main.Citation_Key = Citation_Activity.Citation_Key
        Where --ExportViaDAT = 'N' And 
        Citation_Main.Void ='N' And Citation_Main.Warning ='N'
        AND (Citation_Main.User_Defined5 ='N' or Citation_Main.User_Defined5 ='' or Citation_Main.User_Defined5 is NULL)
        ORDER BY Citation_Main.Issue_Date DESC

I have been reading the suggested SO posts that appeared as I was entering this post, but could not see anything wrong with this part of the stored procedure.

My question is, is there something inherently wrong with checking for NULL in this part of the stored procedure?

octopusgrabbus
  • 10,555
  • 15
  • 68
  • 131
  • If I add `select * from (values ('N','N','N',GETDATE())) Citation_Main (Void, Warning, User_Defined5,Issue_Date)` above what you've shown to make it a complete query, it returns a row. We can't reproduce what you're seeing just from this fragment. – Damien_The_Unbeliever Nov 16 '18 at 15:35
  • Nothing wrong with checking it if you want to update rows where it's N, '', or NULL. If the file isn't written when the predicate = 'N' then another part of your where clause is preventing this, like the Void or Warning – S3S Nov 16 '18 at 15:35
  • I added some more for clarity. – octopusgrabbus Nov 16 '18 at 15:44
  • The rest of your code doesn't change anything unfortunately. If you expect the rows `where Citation_Main.User_Defined5 ='N'` to be returned and they aren't, then the issue is in the other part of the where clause...`Citation_Main.Void ='N' And Citation_Main.Warning ='N'`. But to answer your question about `NULL`, it doesn't perform in a *special* way that would cause your issue here. – S3S Nov 16 '18 at 15:46
  • 1
    Or with that `join` to `Citation_Activity` excluding such rows. But you're the only one who can look at your data, unless or until you add some sample data to your question also that *demonstrates* the issue. – Damien_The_Unbeliever Nov 16 '18 at 15:47
  • 2
    This is trivially easy to debug. Take the cursor declaration and copy it to a query window. Remove the cursor declare part and just make it a single select statement. Go find a PK value from citation_main that you **know** should be included in the resultset and add that value to the where clause as a condition. Then comment out the user_defined5 logic in the where clause and add that column to the resultset. What do you see? – SMor Nov 16 '18 at 15:56
  • 1
    Are you using a case-sensitive collation? To find out, [see here](https://stackoverflow.com/questions/1411161/sql-server-check-case-sensitivity)? If so, make sure the column contains a capital N in your testing. – BoCoKeith Nov 17 '18 at 01:22
  • @BoCoKeith Sorry for the delay in responding. It's Thanksgiving prep time. I ran a query on the table in question. 'N' was in the field, not 'n'. – octopusgrabbus Nov 19 '18 at 23:02

0 Answers0